TSQL

TSQL – Utility to read csv and Excel files

/*
If you see this error?
The 32-bit OLE DB provider “Microsoft.ACE.OLEDB.12.0″ cannot be loaded in-process on a 64-bit SQL Server.

To resolve this error you need to get the “Access Database Engine 2010″ to facilitate the transfer of data between existing Microsoft Office files such as “Microsoft Office Access 2010″ (*.mdb and *.accdb) files and “Microsoft Office Excel 2010″ (*.xls, *.xlsx, and *.xlsb) files to other data sources such as “Microsoft SQL Server”.
Get the Microsoft Access Database Engine 2010 Redistributable [http://www.microsoft.com/en-us/download/details.aspx?id=13255]

http://sqlwithmanoj.com/tag/microsoft-ace-oledb-12-0/
*/
IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘sp_ReadCSVFile’)
DROP PROCEDURE sp_ReadCSVFile
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘sp_ReadExcelFile’)
DROP PROCEDURE sp_ReadExcelFile
GO

IF EXISTS (
SELECT * FROM sysobjects WHERE id = object_id(N’fn_SplitString’)
AND xtype IN (N’FN’, N’IF’, N’TF’)
)
DROP FUNCTION fn_SplitString
GO

/*

Remember to put a file Schema.ini in same folder as csv file. Also ‘@TargetTable’ parameter should be csv file name (but without Extension)

exec sp_ReadCSVFile ‘C:\tmp’, ‘EmpData’

[EmpData.csv]
FORMAT=Delimited(;)
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI
TextDelimiter=`
DecimalSymbol=.
COL1=EmpId TEXT
COL2=FirstName TEXT
COL3=LastName TEXT
COL4=MiddleName TEXT
COL5=EmailAddr TEXT
*/
CREATE PROCEDURE sp_ReadCSVFile
(
@TargetFolder varchar(255),
@TargetTable varchar(255)
)
AS
BEGIN
declare @SqlStmt nvarchar(max)

set @SqlStmt = ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”[dbo].[‘ + @TargetTable + ‘]”) AND type in (N”U”))’
set @SqlStmt = @SqlStmt + ‘ TRUNCATE TABLE ‘ + @TargetTable
PRINT @SqlStmt
exec sp_executesql @SqlStmt

set @SqlStmt = ‘ SELECT * INTO ‘ + @TargetTable
+ ‘ FROM OpenDataSource (”Microsoft.ACE.OLEDB.12.0”, ”Data Source=”‘ + @TargetFolder + ‘”;Extended properties=Text”)…[‘
+ @TargetTable + ‘#csv]’
PRINT @SqlStmt
exec sp_executesql @SqlStmt
END
GO

/*
Usage:
exec sp_ReadExcelFile ‘C:\tmp’, ‘EmpData’, ‘EmpData.xls’, ‘[Sheet1$]’, ‘Col1,Col2,Col3,Col4,Col5’
*/
CREATE PROCEDURE sp_ReadExcelFile
(
@TargetFolder varchar(255), /* For example, ‘C:\tmp’ */
@TargetTable varchar(255), /* For example, ‘EmpData’ */
@ExcelFile varchar(255), /* For example, ‘EmpData.xls’ */
@ExcelSheet varchar(255), /* For example, ‘[Sheet1$]’ */
@ExcelFields varchar(8000) /* Comma separate list, for example: ‘Col1,Col2,Col3,Col4,Col5’ */
)
AS
BEGIN
declare @SqlStmt nvarchar(max)
declare @FirstColumn nvarchar(255)

set @SqlStmt = ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”[dbo].[‘ + @TargetTable + ‘]”) AND type in (N”U”))’
set @SqlStmt = @SqlStmt + ‘ DROP TABLE ‘ + @TargetTable
PRINT @SqlStmt
exec sp_executesql @SqlStmt

select top 1 @FirstColumn=[part] from fn_SplitString(@ExcelFields, ‘,’)
print ‘FirstColumn: ‘ + @FirstColumn

set @SqlStmt = ‘SELECT ‘ + @ExcelFields + ‘ INTO ‘ + @TargetTable + ‘ FROM OPENROWSET(”Microsoft.ACE.OLEDB.12.0”,
”Excel 12.0;DATABASE=’ + @TargetFolder + ‘\’ + @ExcelFile + ‘;IMEX=1”, ”Select * from ‘ + @ExcelSheet + ”’)’
+ ‘WHERE NOT ‘ + @FirstColumn + ‘ IS NULL’
PRINT @SqlStmt
exec sp_executesql @SqlStmt
END
GO

/*
Taken from: http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql
Usage: select top 1 part from fn_SplitString(‘aaa,bbb,ccc,ddd,eee’,’,’)
*/
CREATE FUNCTION [dbo].[fn_SplitString]
(
@sString nvarchar(2048),
@cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
if @sString is null return
declare @iStart int,
@iPos int
if substring( @sString, 1, 1 ) = @cDelimiter
begin
set @iStart = 2
insert into @tParts
values( null )
end
else
set @iStart = 1
while 1=1
begin
set @iPos = charindex( @cDelimiter, @sString, @iStart )
if @iPos = 0
set @iPos = len( @sString )+1
if @iPos – @iStart > 0
insert into @tParts
values ( substring( @sString, @iStart, @iPos-@iStart ))
else
insert into @tParts
values( null )
set @iStart = @iPos+1
if @iStart > len( @sString )
break
end
RETURN
END
GO

Advertisements

Looking for a SQL parser for C#? dotnet?

If you dig around a little you’d quickly find that there isn’t any free SQL parser library around.

Commercial parsers for example General SQL Parser http://www.sqlparser.com costs around USD450+(But thing is, it supports all major database vendor specific SQL syntax)
Microsoft TSql100Parser (http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.tsql100parser(v=vs.100).aspx) is nice but you’d need Microsoft.Data.Schema.ScriptDom.dll amd Microsoft.Data.Schema.ScriptDom.Sql.dll – unfortunately you need Visual Studio Team System 2008 Database Edition to have access to these two dll’s.
Offers from Canadian Developer Sergey Gorbenko via Code Project is free (http://www.codeproject.com/Articles/32524/SQL-Parser) but unfortunately only exposes OrderByClause and WhereClause

There are couple Open Source SQL Parsers, but nothing from dotnet.

Java

http://jsqlparser.sourceforge.net/

http://zql.sourceforge.net/

C++

https://parsersql.codeplex.com/

If you want a dotnet SQL parser, and you don’t want to pay for it (Or that you prefer open source), then your only options are Irony parser http://irony.codeplex.com/ (Generic language parser), with SqlGrammar (https://github.com/Alxandr/Irony/blob/master/Irony.Samples/SQL/SqlGrammar.cs)

Even legendary Scott Hanselman also written a piece on this http://www.hanselman.com/blog/TheWeeklySourceCode59AnOpenSourceTreasureIronyNETLanguageImplementationKit.aspx
However, it’s not without limitations. Here’s a simple test drive.

Example 1: Simple SELECT
SELECT Col1, Col2, Col3 FROM SomeTable WHERE SomeTable=1

From Visual Studio Watch Window:
“whereClauseOpt” = ExprTree.Root.ChildNodes[0].ChildNodes[5].Term.Name
SomeTable = ExprTree.Root.ChildNodes[0].ChildNodes[5].ChildNodes[1].ChildNodes[0].ChildNodes[0].Token.Text
“=” = ExprTree.Root.ChildNodes[0].ChildNodes[5].ChildNodes[1].ChildNodes[1].ChildNodes[0].Token.Text
1 = ExprTree.Root.ChildNodes[0].ChildNodes[5].ChildNodes[1].ChildNodes[2].Token.Value

Example 2: Alias handling
This is OK:
SELECT Col1, Col2, Col3 FROM SomeTable left join SomeOtherTable on SomeTable.Id=SomeOtherTable.Id WHERE SomeTable=1
This is also OK (Column alias):
SELECT Col1 as XXX, Col2 as YYY, Col3 as ZZZ FROM SomeTable left join SomeOtherTable on SomeTable.Id=SomeOtherTable.Id WHERE SomeTable=1
SELECT Col1 XXX, Col2 YYY, Col3 ZZZ FROM SomeTable left join SomeOtherTable on SomeTable.Id=SomeOtherTable.Id WHERE SomeTable=1

This failed (Column Alias) with ParserMessages ‘Syntax error, expected: ., AS, id_simple, ,, INTO, FROM, WHERE, GROUP, HAVING, ORDER, ), ;, CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, GO
SELECT Col1 ‘XXX’, Col2 ‘YYY’, Col3 ‘ZZZ’ FROM SomeTable left join SomeOtherTable on SomeTable.Id=SomeOtherTable.Id WHERE SomeTable=1

This also failed (Table Alias) with ParserMessages ‘Syntax error, expected: ., ), ,, INNER, LEFT, RIGHT, JOIN, WHERE, GROUP, HAVING, ORDER, ;, CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, GO, ON’
SELECT Col1, Col2, Col3 FROM SomeTable a left join SomeOtherTable b on a.Id=b.Id WHERE SomeTable=1

Example 3. Subquery in SELECT list
The following failed with ParserMessages ‘Syntax error, expected: INTO, FROM, WHERE, GROUP, HAVING, ORDER, ), ;, CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, GO’

SELECT *, (select productName from Product where Orders.ProductId=Product.ProductId) Last_OrderDate FROM Orders

Example 4. Subquery in FROM clause
The following failed with ParserMessages ‘Syntax error, expected: id_simple’

SELECT ProductId, ProductName FROM (select top 10 ProductId, ProductName from Products) as SomeTable

Example 5. Subquery in WHERE clause
This following statement was parsed successfully!

SELECT OrderId, ProductName FROM Orders WHERE CustomerId in (SELECT CustomerId FROM BannedCustomers)

Example 6. Stored Procedures and UDF’s calls?
They all failed with ParserMessages ‘Syntax error, expected: CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, GO’

EXEC sp_SomeStoredProc
EXEC SomeDB..sp_SomeStoredProc

Also tried function/UDF calls:
EXEC @ret = dbo.ufnGetSalesOrderStatusText @Status= 5

Example 7. Declare variables?
Failed with ParserMessages ‘Syntax error, expected: CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, GO’

DECLARE @ret nvarchar(15)= NULL;

Example 8. CREATE PROCEDURE|FUNCTION|VIEW
The following failed with ParserMessages ‘Syntax error, expected: TABLE, UNIQUE, INDEX’

CREATE PROCEDURE spSelectStudent (@Course INTEGER, @Grade INTEGER) as BEGIN SELECT * FROM Courses WHERE Std_Course=@Course AND Std_Grade <= @Grade END
CREATE PROC spSelectStudent (@Course INTEGER, @Grade INTEGER) as BEGIN SELECT * FROM Courses WHERE Std_Course=@Course AND Std_Grade <= @Grade END
CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int; SET @ISOweek= DATEPART(wk,@DATE)+1; RETURN(@ISOweek);RETURN(@ISOweek); END
CREATE VIEW view_name AS SELECT Col1, Col2, Col3 FROM table_name WHERE Id<100

Example 9. USE SomeDatabase GO
Failed with ParserMessages ‘Syntax error, expected: CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, GO’

Quick scans at SqlGrammar following constructs also not supported:
a. CURSOR
b. IF-ELSE
c. TRY-CATCH

Example 10. And, of course, if your SQL is dynamically concatenated…

declare @stmt as varchar(255)

declare @SomeTable as varchar(25)

set @SomeTable = “TABLE_ABC”

set @stmt = “SELECT * FROM ” + @SomeTable

 

Almost forgot to mention … Irony is great. Except you need to drill quite deep into the object’s attribute hierarchy to find the Select|Where|GroupByClause’s …

This said, you need to do some digging into the object’s attribute hierarchy, even if you pay license fee for a commercial SQL parser such as http://www.sqlparser.com/.

Lets say you want to parse this SQL statement and identify Source tables and the one Destination table.
insert into Table1 select Col1, Col2, Col3 from Table2 a inner join (select Col3 from Table3 left join Table4 on Table3.DT=Table4.DT where Table4.Col4='US') b on a.ID=b.ID

With General SQL Parser, table nodes are of type TLz_Attr, with Location = TLzOwnerLocation.eltable. However, there’s nothing in the object’s attribute hierarchy with which you can distinguish Destination tables from Source table. You need to hard code the logic in your code: First table is the one Destination table.

Adding to the challenge – imagine your objective, in additional to extracting Source tables/Destination table, that you also need to track which field(s) is coming from which Source table?

Microsoft SQL Server and TSQL Refresher

1. Normalization/denormalization – example? 1/2/3NF
1NF Primary Key and no repeating group of data
2NF No partial dependency of non-key field on one of the primary keys
3NF No partial dependency among non-key fields

http://www.tutorialspoint.com/sql/first-normal-form.htm
http://databases.about.com/od/administration/l/bldef_2nf.htm
http://www.tutorialspoint.com/sql/second-normal-form.htm
http://www.tutorialspoint.com/sql/third-normal-form.htm

2. Recovery Model?
Full: Transaction by transaction, restore to Point-in-Time by restore last database backup, then transaction logs.
Bulk Logged: Bulk operations not logged to speed things up. You can still restore to Point-in-Time if no bulk operation between last database or transaction log backup.
Simple: Only minimal logging to transaction log. No Point-in-Time restore. Anything happens you can only recover up to last database backup.

http://www.todo-backup.com/backup-resource/sql-backup-software/three-sql-server-recovery-models.htm
http://www.mssqltips.com/sqlservertutorial/5/sql-server-bulklogged-recovery-model/

3. Checkpoint – “For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.
The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal.”
http://msdn.microsoft.com/en-us/library/ms189573(v=sql.110).ASPX

4. Backup Database?
http://www.mssqltips.com/sqlservertutorial/24/creating-a-backup-using-sql-server-command-line-tsql/

BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\AdventureWorks.BAK’
GO

BACKUP LOG AdventureWorks
TO DISK = ‘C:\AdventureWorks.TRN’
GO

Backup Log? ldf -> trn
BACKUP LOG AdventureWorks
TO DISK = ‘C:\AdventureWorks.TRN’
GO

BACKUP LOG AdventureWorks
TO DISK = ‘C:\AdventureWorks.TRN’
WITH PASSWORD = ‘Q!W@E#R$’
GO

BACKUP LOG AdventureWorks
TO DISK = ‘C:\AdventureWorks.TRN’
WITH STATS
GO

BACKUP LOG AdventureWorks
TO DISK = ‘C:\AdventureWorks.TRN’
WITH STATS = 1
GO

BACKUP LOG AdventureWorks
TO DISK = ‘C:\AdventureWorks.TRN’
WITH DESCRIPTION = ‘Log backup for AdventureWorks’
GO

http://www.mssqltips.com/sqlservertutorial/21/sql-server-backup-log-command/
http://www.mssqltips.com/sqlservertutorial/8/sql-server-transaction-log-backups/
http://www.mssqltips.com/sqlservertip/1318/automating-transaction-log-backups-for-all-sql-server-databases/

3. Error ‘TRUNCATE_ONLY’ is not a recognized BACKUP option. when running following command. Why?
DBCC SHRINKFILE(TestDBLog, 1)
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TestDBLog, 1)

ANS:
“No, it’s not supported any more in sql 2008, if you want to remove log chain (Without proper log backup), try to switch to simple recovery mode.”
(a) Get Around
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
(b) Take proper log backup
BACKUP LOG [TestDb] TO DISK = N’C:\Backup\TestDb.bak’

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/54e6e281-005a-4b07-8c85-3685441f4f3d/truncateonly-is-not-a-recognized-backup-option?forum=sqldatabaseengine
http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/

4. Shrink Database?
DBCC SHRINKDATABASE (UserDB, 10);
GO

http://msdn.microsoft.com/en-us//library/ms190488.aspx

5. Shrink Log file?
“To avoid filling up the transaction log of a database, routine backups are essential. Under the simple recovery model, log truncation occurs automatically after you back up the database, and under the full recovery model, after you back up the transaction log.”

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName>
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/
http://msdn.microsoft.com/en-us/library/ms186865.aspx
6. Restore Database?

CASE 1: RESTORE with file move:
Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
GO

Step 2: Use the values in the LogicalName Column in following Step.
—-Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

—-Restore Database
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.ldf’

ALTER DATABASE YourDB SET MULTI_USER
GO

CASE 2: RESTORE Differential
RESTORE DATABASE AdventureWorks FROM DISK = ‘C:\AdventureWorks.BAK’ WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = ‘C:\AdventureWorks.DIF’
GO

CASE 3. Restore Transaction Log, to point in time
RESTORE DATABASE NewDatabase
FROM DISK = ‘D: \BackupFiles\TestDatabaseFullBackup.bak’
WITH
MOVE ‘PreviousDatabase’ TO ‘D:\DataFiles \TestDatabase.mdf’,
MOVE ‘PreviousDatabase_log’ TO ‘D:\DataFiles \TestDatabase_Log.ldf’,
NORECOVERY

RESTORE LOG NewDatabase
FROM DISK = ”D: \BackupFiles\TestDatabase_TransactionLogBackup1.trn’
WITH NORECOVERY

RESTORE LOG NewDatabase
FROM DISK = ”D: \BackupFiles\ TestDatabase_TransactionLogBackup2.trn’
WITH NORECOVERY

RESTORE LOG NewDatabase
FROM DISK = ”D: \BackupFiles\ TestDatabase_TransactionLogBackup3.trn’
WITH NORECOVERY

RESTORE LOG NewDatabase
FROM DISK = ”D: \BackupFiles\ TestDatabase_TransactionLogBackup4.trn’
WITH RECOVERY

RESTORE LOG NewDatabase
FROM DISK = ”D: \BackupFiles\ TestDatabase_TransactionLogBackup4.trn’
WITH STOPAT = N’6/28/2007 4:01:45 PM’, RECOVERY

http://www.codeproject.com/Articles/513248/Dynamically-restore-full-and-all-transaction-log-b
http://www.techrepublic.com/blog/the-enterprise-cloud/restore-your-sql-server-database-using-transaction-logs/

7. RESTORE vs ATTACH?
“Attach” manually select mdf/ndf and ldf
RESTORE restores from BAK and TRN (log)

8. Do we have “Parametetrized View” in TSQL?
* Closest is inline table UDF
http://stackoverflow.com/questions/4498364/create-parameterized-view-in-sql-server-2008

9. Advantages and Disadvantages of Stored Procedures?
Pros
* Reduce network traffic
* Code re-use
* Single point of control
* Compiled on first run
* Protection against SQL Injection
* Easy to unit test on SQL layer.

Cons
* Most database vendors offer different flavors of SQL. M$ SQL’s TSQL, Oracle PL SQL…etc.
Application logic wrapped in application layer can be database vendor neutral. However, stored procedures syntax isn’t.
* If you’re using Microsoft SQL, ability to debug stored procedures from Query Analyzer in SQL 2000 and SQL Management Studio 2008 or later.
SQL 2005 is the one edition when this ability was removed.
* When you’re running .NET code in debugger, you can STEP INTO stored procedure’s definition – meaning you’d have to debug the stored procedure separately.

http://dumindag.wordpress.com/2009/11/03/advantages-and-disadvantages-of-using-stored-procedures/
http://blog.sqlauthority.com/2009/11/08/sql-server-stored-procedure-are-compiled-on-first-run-sp-taking-longer-to-run-first-time/
http://www.diaryofaninja.com/blog/2010/11/23/debugging-sql-queries-function-amp-stored-procedures-with-sql-management-studio
http://stackoverflow.com/questions/2016879/debugging-stored-procedures-in-management-studio
http://stackoverflow.com/questions/273437/how-do-you-debug-mysql-stored-procedures

10. Stored Procedure vs UDF’s?
* Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called.
But Function is compiled and executed every time when it is called.
* Stored Procedures can contain try-catch block, not in UDF’s
* Stored Procedure can have input+output parameters. UDF’s only input parameters.
* UDF’s must return a result, whether scalar or TABLE, which can be nested in SELECT or WHERE clause of another statement.

http://www.dotnet-tricks.com/Tutorial/sqlserver/7EDL150912-Difference-between-Stored-Procedure-and-Function-in-SQL-Server.html

11. Database Encryption – Transparent Data Encryption (TDE) – “TDE enables you to encrypt an entire database. Backups for databases that use TDE are also encrypted. TDE protects the data at rest, which means that the database’s data and log files are encrypted using the AES and 3DES encryption algorithms. TDE is completely transparent to the application and requires no coding changes to implement.”
http://sqlmag.com/database-security/sql-server-encryption-options
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/

12. Encrypt Column of Data
http://technet.microsoft.com/en-us/library/ms179331.aspx

13. Dynamic Management View?

14. Why CURSOR sucks?
“set-based processing”
“row-based processing” –> CURSOR, slow

15. Primary Key vs Unique Constraints
Primary Key:
Can be only one in a table
It never allows null values
Primary Key is unique key identifier and can not be null and must be unique.

Unique Key:
Can be more than one unique key in one table.
Unique key can have null values
It can’t be candidate key
Unique key can be null and may not be unique.

16. Primary Key vs Clustered Index?

17. The bitwise operators in SQL Server are:
& (Bitwise AND)
~ (Bitwise NOT)
| (Bitwise OR)
^ (Bitwise Exclusive OR)

http://www.mssqltips.com/sqlservertip/1218/sql-server-bitwise-operators-store-multiple-values-in-one-column/

18. Synonyms
“Changing the name of an table once an application has been deployed has traditionally been a difficult task in SQL Server 2000. The typical solution in SQL Server 2000 for referencing a different object was to use a View.”
But with renamed View, you can’t do INSERT
http://www.mssqltips.com/sqlservertip/1076/how-and-why-should-i-use-sql-server-2005-synonyms/
http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/

19. char(255) vs varchar(255) vs varchar(MAX) http://www.codeproject.com/Messages/4486417/char-255-vs-varchar-255-vs-varchar-MAX.aspx
Note
* varchar has null termination character, char don’t
* varchar(MAX) = 8000 bytes, nvarchar(MAX=4000) = 8000 bytes

20. Why not varchar(MAX) instead of varchar(n) everywhere? Index Key size < 900 bytes
http://stackoverflow.com/questions/2091284/varcharmax-everywhere

21. TEXT, NTEXT vs VARCHAR(MAX)

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

TEXT/NTEXT/IMAGE deprecated in SQL 2012: http://technet.microsoft.com/en-us/library/ms143729.aspx
22. DATALENGTH() to get data length in TEXT/NTEXT/IMAGE columns?

http://www.mssqltips.com/sqlservertip/1188/how-to-get-length-of-text-ntext-and-image-columns-in-sql-server/

23. Heap Table? Clustered vs Non-clustered vs Xml
Table without a clustered-index is called a “heap table”. A heap table has no sorted data thus SQL server has to scan the entire table in order to locate the data in a process called a “scan”. In the case of a clustered index the data are sorted on the key values (columns) of the index. SQL server is now able to locate the data by navigating down from the root node, to the branch and finally to the leaf nodes of the B-tree structure of the index, in a process called a “seek”. The later approach is much faster when you want to filter or sort the data you want to retrieve.
A non-clustered index, stored in separate location, containing only a subset of columns and a row locator to the table’s rows or to the clustered index’s key.

http://msdn.microsoft.com/en-us/library/ms175049.aspx
http://www.karafilis.net/sql-indexing-part2/

24. B-tree – a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. The B-tree is a generalization of a binary search tree in that a node can have more than two children
http://www.youtube.com/watch?v=coRJrcIYbF4&hd=1

25. IndexSeek vs. IndexScan, Selectivity/Cadinality http://www.bennadel.com/blog/1424-Exploring-The-Cardinality-And-Selectivity-Of-SQL-Conditions.htm
Cardinality of a column = # rows in table / # distinct values in that column
(Max Cardinality = 1.0, the closer it get to 1.0 the more selective)

26. ACID = ?

Atomic/Consistent/Isolated/Durable

http://en.wikipedia.org/wiki/ACID

27. Isolation Level –
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED|REPEATABLE READ|SERIALIZABLE
Read Uncommitted – Dirty read
Read Committed [Default] – Share lock (On rows matching WHERE clause only, not table lock) released as soon as read operation done but before transaction completed
Repeatable Read – An additional guarantee that any data read cannot change (i.e. it is repeatable) for the duration of the transaction.
It does this by maintaining the Shared lock (On rows matching WHERE clause only, not table lock) for the duration of the transaction (i.e. read done + transaction completed)
Can have Phantom rows.
Serializable – It prevents phantoms by taking a range lock or table level lock if range lock can’t be acquired (i.e. no index on the predicate column) for the duration of the transaction.
No phantoms.

http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx
http://gavindraper.com/2012/02/18/sql-server-isolation-levels-by-example/

28. Shared/Update/Exclusive/Intent locks?

sp_lock to check realtime locking information.

Example of locking hint:
SELECT * FROM Person.Address WITH (HOLDLOCK|UPDLOCK)
WHERE AddressId < 2

Share Lock – multiple threads can acquire Share Lock on same piece of data concurrently
Update Lock – Only one update lock can be held on the data at one time, similar to an exclusive lock. But the difference here is that the update lock itself can’t modify the underlying data.
Intent lock – An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

http://msdn.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx
http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
http://msdn.microsoft.com/en-us/library/ms187373.aspx
http://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx
http://sqlblog.com/blogs/kalen_delaney/archive/2009/09/11/what-do-you-intend-with-that-lock.aspx

29. Pessimistic Lock and Optimistic Lock?
http://www.codeproject.com/Articles/114262/6-ways-of-doing-locking-in-NET-Pessimistic-and-opt

30. @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
31. CREATE TABLE, PK vs UNIQUE and general constraints, FILLFACTOR
PK: http://blog.sqlauthority.com/2007/10/16/sql-server-three-t-sql-script-to-create-primary-keys-on-table/
IF OBJECT_ID (‘dbo.Vendors’, ‘U’) IS NOT NULL
DROP TABLE dbo.Vendors;
GO
CREATE TABLE dbo.Vendors
(
VendorID int PRIMARY KEY,
VendorName nvarchar (50),
CreditRating tinyint,
Country char(3),

constrant CK_Vendor_CreditRating check(CreditRating>0 AND CreditRating<100)
)
GO

ALTER TABLE dbo.Vendors ADD CONSTRAINT CK_Vendor_CreditRating
CHECK (CreditRating > 0 AND CreditRating < 100)

CREATE NONCLUSTERED INDEX idx_VendorCountry on Vendors (Country)

CREATE [CLUSTERED|NONCLUSTERED] INDEX ix_tableName__columnName ON tableName (columnName)

CREATE TRIGGER (For auditing/validations) – http://msdn.microsoft.com/en-us/library/ms189799.aspx
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘AdventureWorks2012 Administrator’,
@recipients = ‘danw@Adventure-Works.com’,
@body = ‘Don”t forget to print a report for the sales force.’,
@subject = ‘Reminder’;
32. UDF
– Scalar functions
– Inline Table-valued functions
– Multi-statement Table-valued functions

CREATE FUNCTION [dbo].[ufn_GetContactOrders](@ContactID int)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Orders varchar(500)
SELECT @Orders = COALESCE(@Orders + ‘, ‘, ”) + CAST(SalesOrderID as varchar(10)) WHERE ContactID = @ContactID
RETURN (@Orders)
END

CREATE FUNCTION [dbo].[ufn_itv_GetContactSales](@ContactID int)
RETURNS TABLE
AS
RETURN (
SELECT h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate],
h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID]
FROM Sales.SalesOrderHeader AS h
JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID
JOIN Production.Product AS p ON p.ProductID = d.ProductID
WHERE ContactID = @ContactID )

CREATE FUNCTION [dbo].[ufn_mtv_GetContactSales](@ContactID int)
RETURNS @retSalesInfo TABLE (
[ContactID] INT NOT NULL,
[SalesOrderID] INT NULL,
[ProductID] INT NULL,
[Name] NVARCHAR(50) NULL,
[OrderDate] DATETIME NULL,
[DueDate] DATETIME NULL,
[ShipDate] DATETIME NULL,
[TotalDue] MONEY NULL,
[Status] TINYINT NULL,
[SalesPersonID] INT NULL)
AS
BEGIN
IF @ContactID IS NOT NULL
BEGIN
INSERT @retSalesInfo
SELECT h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate],
h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID]
FROM Sales.SalesOrderHeader AS h
JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID
JOIN Production.Product AS p ON p.ProductID = d.ProductID
WHERE ContactID = @ContactID
END
— Return the recordsets
RETURN
END

http://sqlwithmanoj.wordpress.com/2010/12/11/udf-user-defined-functions/
33. CURSOR types [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
http://www.sql-server-performance.com/2007/cursors/

34. CTE

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)
AS
(
SELECT id, firstname, lastname FROM table
)
SELECT * FROM ExampleCTE

http://sqlwithmanoj.wordpress.com/2011/05/23/cte-recursion-sequence-dates-factorial-fibonacci-series/
35. Ranking functions ROW_NUMBER,RANK,DENSE_RANK,NTILE:
Example 1: Simple ROW_NUMBER

WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
FROM Sales.SalesOrderHeader
)
SELECT SalesOrderID, OrderDate, RowNumber
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

Example 2: PARTITION
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1),
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
ORDER BY TerritoryName;

WHERE vs HAVING
WHERE is applied before GROUPBY, HAVING is applied after.

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

http://msdn.microsoft.com/en-us/library/ms175126.aspx
http://sqlwithmanoj.wordpress.com/2010/08/09/ranking-functions-with-sql-server-2005/
http://thehobt.blogspot.hk/2009/02/rownumber-rank-and-denserank.html
http://markvsql.com/2009/02/the-coolness-of-the-t-sql-ntile-function/

36. PIVOT

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)
— insert some sample data
select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

CUBE,ROLLUP,GROUPING SET http://sqlwithmanoj.wordpress.com/2010/11/12/cube-rollup-compute-compute-by-grouping-sets/
select class, section, sum(marks) [sum]
from #tempTable
group by class, section [with CUBE|ROLLUP]

TRUNCATE vs DELETE – TRUNCATE reset IDENTITY

ISNULL vs NULLIF http://blog.sqlauthority.com/2007/06/22/sql-server-explanation-and-comparison-of-nullif-and-isnull/
ISNULL – returns replacement if arg1 is NULL
select isnull(NULL, ‘N/A’) –> Returns ‘N/A’
NULLIF – returns null if arg1 and arg2 equals
select nullif(‘abc’, ‘abc’) –> Returns null

ISNULL vs COALESCE http://sqlwithmanoj.wordpress.com/2010/12/23/isnull-vs-coalesce/
DECLARE @str1 VARCHAR(10), @str2 VARCHAR(10)

— ISNULL() takes only 2 arguments
SELECT ISNULL(@str1, ‘manoj’) AS ‘IS_NULL’ — manoj

— COALESCE takes multiple arguments and returns first non-NULL argument
SELECT COALESCE(@str1, @str2, ‘manoj’) AS ‘COALESCE’ — manoj

— ISNULL() equivalent of COALESCE, by nesting of ISNULL()
SELECT ISNULL(@str1, ISNULL(@str2, ‘manoj’)) AS ‘IS_NULL eqv’ — manoj

EXECUTE vs sp_executesql (OUTPUT) http://support.microsoft.com/kb/262499
SET @SQLString = N’SELECT @LastlnameOUT = max(lname)
FROM pubs.dbo.employee WHERE job_lvl = @level’
SET @ParmDefinition = N’@level tinyint,
@LastlnameOUT varchar(30) OUTPUT’
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

37. Send email alerts? sp_send_dbmail
Execute operating system command?
insert into exec xp_cmdshell ‘dir c:\*.exe’

38. GETDATE (ms) vs SYSDATETIME (nano sec)

http://blog.sqlauthority.com/2010/06/10/sql-server-difference-between-getdate-and-sysdatetime/

39. INTERSECT and EXCEPT

http://www.mssqltips.com/sqlservertip/1327/compare-sql-server-datasets-with-intersect-and-except/

40. TRY-CATCH, ERROR_MESSAGE,ERROR_NUMBER,ERROR_SEVERITY, RAISEERROR
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@Time_Stamp = GETDATE();
CASE statement
SELECT
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN ‘M’ THEN ‘Male’
WHEN ‘F’ THEN ‘Female’
END
FROM Employees

http://www.mssqltips.com/sqlservertip/1027/sql-server-2005-try-and-catch-exception-handling/
http://www.mssqltips.com/sqlservertip/1152/standardized-sql-server-error-handling-and-centralized-logging/
http://msdn.microsoft.com/en-us/library/ms178592%28v=sql.90%29.aspx

41. GO statement and multiple batches – what’s it for?
http://www.mssqltips.com/sqlservertip/1216/executing-a-tsql-batch-multiple-times-using-go/

http://technet.microsoft.com/en-us/library/ms188037.aspx

42. CREATE SYNONYM

http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/

43. DELETE duplicates on table with no PK via SET ROWCOUNT

http://www.mssqltips.com/sqlservertip/1103/delete-duplicate-rows-with-no-primary-key-on-a-sql-server-table/

44. Performance Tuning
DBCC DBREINDEX – deprecated, use ALTER INDEX instead.
http://msdn.microsoft.com/en-us/library/ms181671.aspx

Change from:
DBCC DBREINDEX(‘$TABLENAME$,’ ‘,0.75)
To:
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;

ALTER INDEX ALL ON ‘$TABLENAME$’ REBUILD WITH (FILLFACTOR = 0.75)
http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

DBCC INDEXDEFRAG – Defrag index, deprecated, use ALTER INDEX instead: http://msdn.microsoft.com/en-us/library/ms177571.aspx

DBCC SHOWCONTIG – Shows fragmentation statistics. Deprecated, use “sys.dm_db_index_physical_stats” instead.
http://msdn.microsoft.com/en-us/library/ms175008.aspx
http://msdn.microsoft.com/en-us/library/ms188917.aspx

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N’AppliedAlgoDb’);
SET @object_id = OBJECT_ID(N’PK_DownloadStats’);
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘LIMITED’);

Query Execution Plan –> Specific Query: Look for Table Scan. Issues with JOIN’s? Percent Cost…etc
https://www.youtube.com/watch?v=J69zLvoh2oI
http://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/
http://technet.microsoft.com/en-us/library/ms175913(v=sql.105).aspx

SQL Profiler –> Look for queries which hits many times, or queries which takes long time to run. Issues with JOIN’s?

Database Engine Tuning Advisor –> Give a workload, create statistics & indexes
https://www.youtube.com/watch?v=xzXNRWuzIyE

Perfmon – Disk Activity?
Excessive paging can be observed via Perfmon. Add Counter: LogicalDisk\Avg Queue Length\_Total
(If reading >2 consistently, you probably need to review your job )

45. Audit
a. Server role member

EXEC sp_helpsrvrolemember

b. Following run against sys views in User database (not Master database)

Table schema

select
TableList.TABLE_SCHEMA as SchemaName,
TableList.TABLE_NAME as TableName,
COL.COLUMN_NAME as ColumnName,
COL.ORDINAL_POSITION as OrdinalPosition,
COL.COLUMN_DEFAULT as ColumnDefault,
COL.IS_NULLABLE as IsNullable,
COL.DATA_TYPE as DataType,
COL.CHARACTER_MAXIMUM_LENGTH as CharMaxLength,
COL.NUMERIC_PRECISION as NumericPrecision,
COL.DATETIME_PRECISION as DateTimePrecision,
COL.CHARACTER_SET_NAME as CharSetName,
COL.COLLATION_NAME as CollationName
FROM INFORMATION_SCHEMA.COLUMNS COL
join (
select
distinct TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
) TableList on TableList.TABLE_NAME = COL.TABLE_NAME and TableList.TABLE_SCHEMA = COL.TABLE_SCHEMA
order by TableList.TABLE_SCHEMA, TableList.TABLE_NAME, COL.ORDINAL_POSITION

Table size

SELECT
sch.name SchemaName,
t.name TableName,
p.rows rowcnt,
SUM(a.total_pages) * 8 as TotalSpaceKB,
SUM(a.used_pages) * 8 as UsedSpaceKB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 as UnusedSpaceKB
FROM sys.tables t
JOIN sys.schemas sch on t.schema_id = sch.schema_id
JOIN sys.indexes i on t.object_id = i.object_id
JOIN sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id

where
t.is_ms_shipped = 0
and i.object_id>255

group by sch.name, t.name, p.rows
order by p.rows desc
List views

select
sch.name as SchemaName,
vws.name as ViewName,
vws.create_date as CreateDate,
vws.modify_date as ModifyDate
from sys.views as vws
join sys.schemas as sch on vws.schema_id = sch.schema_id
order by sch.name, vws.name
View Scheuma

select
sch.name as SchemaName,
vws.name as ViewName,
col.column_id as ColumnId,
col.name as ColumnName
from sys.views as vws
join sys.schemas sch on vws.schema_id = sch.schema_id
join sys.columns col on vws.object_id = col.object_id
order by sch.name, vws.name
Audit Stored Procedures and definitions

select
distinct
ROUTINE_NAME as RoutineName,
ROUTINE_SCHEMA as SchemaName,
ROUTINE_TYPE as RoutineType,
CREATED as CreateDate,
LAST_ALTERED as ModifyDate,
ROUTINE_DEFINITION as RoutineDefinition
FROM INFORMATION_SCHEMA.ROUTINES
order by ROUTINE_SCHEMA, LAST_ALTERED

Happy Coding!