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 go ahead and create the index on the view you just created.
CREATE UNIQUE CLUSTERED INDEX [IDX_ActivityStatusID] ON [dbo].[ViewName]
(
[activityID] ASC,
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Advertisements

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 and say “Run as Administrator”

2. At thepromp type:

SQLCMD -L

This will list all the SQL servers running on your network.

3. To save as csv file

SQLCMD -L > allservers.csv