Improving performance by making use of system catalog

It happened to use LIKE clauses in order to find out the the VIEW's dependency on the base tables.

 information_schema.views.view_definition LIKE    %sys.tables.name%

This caused a huge performance bottle-neck since there is a string search on source code of the views.

Without checking the source code, the result of the above clause can be achieved by using the following system catalog view: VIEW_TABLE_USAGE

This has following columns
exec sp_columns VIEW_TABLE_USAGE 

VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME

Unique ID generation

In SQLServer, the the function newid() can be used for Unique ID generation as given below:

select newid(), a.* from tab1 a;

MINUS clause in SQLServer

DBAs who comes with Oracle background would look for MINUS clause in order to compare the results of the SQL statements that return same type of result sets.

In Oracle, we use

select * from tab1
minus 
select * from tab2;

But, in SQLserver, we have to use EXCEPT clause

select * from tab1
except
select * from tab2;

Helpful Bogs on MSSQL server

I found following blogs more helpful on troubleshooting SQLServer issue:


http://sqlblog.com/blogs/adam_machanic/

https://www.youtube.com/watch?v=3c_VGh8FtZc