Create Indexed Views in SQL Server; SQL Equivalent of Oracle Materialized Views.

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.

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) ;

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