TABLE HINTS SQL SERVER

SQL server doesn't allow to specify index hints on the table that is being updated in an UPDATE statement.

RENAMING COLUMN/TABLE IN SQL SERVER

Renaming a column
sp_rename 'TABLENAME.[COLUMNNAME]','NewColumnName','COLUMN'

Renaming a table/stored procedure any other object

sp_rename 'oldObjectName', 'newObjectName'

Bulk Inserting/Loading data from Flat files

Following are the possible ways in which one can bulk insert data from flat/text files into SQL Server.
1. Using Command line "bcp" utility
2. Using Transact-SQL "Bulk insert" command
3. Using SSIS/DTS packages

DTS packages have certain programmability limitations and don't always offer the best performance.

BULK INSERT gives optimal performance over DTS/SSIS packages.

BULK INSERT
----------------
Bulk insert doesn't create the table automatically. It expects the table to be existed already.

Format file structure














For more details on format file, visit the below page
http://msdn.microsoft.com/en-us/library/ms179250.aspx

Scenario 1: n - # columns in table, m - #columns in text file (1:1)
assume n=m = 3
Then format file

9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100      "\t"    2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Scenario 2:
n>m, To skip a table column
n=3, m=2

Approach 1:

9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Notice that row for "Col2" is missing.

Approach 2:

9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       0        ""      0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

For more details on skipping a database column, visit the below page
http://msdn.microsoft.com/en-us/library/ms179250.aspx

Scenario 3:
n<m, 9.0 4 1 SQLCHAR 0 7 "," 1 col1 "" 2 SQLCHAR 0 100 "," 0 ExtraField SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 "," 2 col2 SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 100 "\r\n" 3 col3 SQL_Latin1_General_CP1_CI_AS For more details on skipping a data filed, visit the below page http://msdn.microsoft.com/en-us/library/ms187908.aspx  


Scenario 4: n=m=4 , order of the table columns is not the same as data fields

9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS

For more information on this scenario, visit the below page
http://msdn.microsoft.com/en-us/library/ms191175.aspx

Running Command line commands from T-SQL

xp_cmdshell can be used to execute command line commands from T-SQL / SQL procedures.
This command can be used if you would like to use "bcp" utility from T-SQL or procedures and if you would like to do any mx record validation, etc...
eg:
xp_cmdshell "dir"

By default "xp_cmdshell" will be disabled.

How to enable
---------------
EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

-- To update the currently configured value for this feature.
RECONFIGURE
GO

For more details on how to enable, refer to the below page.
http://msdn.microsoft.com/en-us/library/ms175046.aspx