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

Cannot bulk load because the file could not be read. Operating system error code (null) SQL Server

Possible reasons
1. Format file expects an end of line after the last row. i.e last row should be empty
2. Column order flat file/data table should be correct. i.e content of the data file should be correct.

Model db in SQL Server

"model" db is created by SQL Server default installation. This db works as a template for any new User databases created.

i.e if you set "Initial file size" attribute of "model" db to 100MB, any new databases created hence forth will have a default value of 100MB for data file size. Similarly any other attributes of database will be inherited from this "model" db.

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