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.

Advertisements

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

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