SQL Server supports creating indexes on a view. It is the same concept as Materialized Views in Oracle. The main difference in the syntax in normal views and indexed views is the clause "with schemabinding" CREATE VIEW [dbo].[ViewName] with schemabinding AS SELECT id, activityID, userID, value, date FROM dbo.TableName WHERE (field = 'activityID') Now you … Continue reading Create Indexed Views in SQL Server; SQL Equivalent of Oracle Materialized Views.
T-SQL
Select Random Rows from Table in SQL Server
To Select 1 Random Records from a table in SQL Server SELECT TOP 1 * FROM Table_Name ORDER BY NEWID() ; To Select 10 Random Records from a table in SQL Server SELECT TOP 10 * FROM tAudit ORDER BY NEWID()
Remove Null Rows SQL Server
DELETE FROM TABLENAME WHERE (COLUMN_NAME IS NULL OR COLUMN_NAME = 0); If you have additional columns containing NULL values and you want to refine your delete criteria DELETE FROM TABLENAME WHERE (COLUMN_NAME IS NULL OR COLUMN_NAME = 0) AND (COLUMN_NAME_1 IS NULL OR COLUMN_NAME_1 = 0) AND (COLUMN_NAME_2 IS NULL OR COLUMN_NAME_2 = 0) ;
Check for Listening Port SQL Server
DECLARE @tcp_port nvarchar(5) EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', @value_name = 'TcpPort', @value = @tcp_port OUTPUT select @tcp_port
Attach Deattach Database T-SQL SQL Server 2005 2008
-- Attach a database USE master; GO CREATE DATABASE ON (FILENAME = 'd:\sql data\dbname.mdf'), (FILENAME = 'd:\sql data\dbname_Log.ldf') FOR ATTACH; GO -- Dettach database and take offline -- USE master; GO ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE go EXEC sp_detach_db @dbname = N'dbname'; GO
Bring database offline online T-SQL
-- Bring Database Offline USE master GO ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE GO -- Bring database online USE master ALTER DATABASE SET ONLINE GO
List all SQL Servers running on your Domain using a single command
Sometimes you want to find all the SQL servers running on your Domain with one quick command. SQLCMD providess facility to do so. Your client workstation should have Microsoft SQL server client installed on it for SQLCMD to work. 1. Go to Command Prompt. If you are on Windows Server 2008. Right Click Command Prompt … Continue reading List all SQL Servers running on your Domain using a single command
Mutiple Inserts. Normal method VS Union ALL method
I know the eternal war between DBA’s and Developers rages on :D, While most developers prefer to make multiple inserts. DBA’s always use UNION all. While highly debatable which is fast performance wise. But if you are doing thousands of insert’s UNION ALL is definitely faster. I have narrowed down inserts from 40 second to … Continue reading Mutiple Inserts. Normal method VS Union ALL method
SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database (via Journey to SQLAuthority)
USE DatabaseName GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default … Read Morevia Journey to SQLAuthority
Find Duplicate Records in SQL Server
Microsoft SQL Server tables should never contain duplicate rows, nor non-unique primary keys. For brevity, we will sometimes refer to primary keys as "key" or "PK" in this article, but this will always denote "primary key." Duplicate PKs are a violation of entity integrity, and should be disallowed in a relational system. SQL Server has … Continue reading Find Duplicate Records in SQL Server
SimpleSQLDBA | Shadab Mohammad 