Bulk Insert to SQL Server From a Text File, Access & Excel

Example for importing data from a text file to SQL server:


Pre-requisites:


1. Destination Table should exist.
2. Flat file data's Data type & no.of.columns should match with Destination Columns Data type & its column count.

BULK INSERT ProductList
FROM 'C:\ProductList.txt'
WITH
(
      FIELDTERMINATOR =',',
      ROWTERMINATOR = '\n'
)

 if you are using any other symbol for separating columns then, you need to specify that as FIELDTERMINATOR. Same way for ROWTERMINATOR.


From MS Access to SQL Server:

INSERT INTO [dbo].[#tblImport]
    Field1,Field2,Field3,Field4
SELECT [Field1],[Field2],[Field3],[Field4] FROM OPENDATASOURCE_
    ('Microsoft.Jet.OLEDB.4.0','C:\AccessFileName.mdb')...[SourceTableName]

Excel To SQL Server & Vice Versa:

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
 @outputfile = 'd:\testing.xls', 
 @query = 'Select * from Database_name..SQLServerTable', 
 @colheaders =1, 
 @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)


5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
 @db_name varchar(100),
 @table_name varchar(100), 
 @file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
 @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
 information_schema.columns
where 
 table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

SQL Server Run Commands Consolidated

To launch SSMS using Command Line:

Go to Command Prompt and type:

SSMS -S SERVERNAME\INSTANCENAME -E

Give your server name & instance name to which you want to connect if any. by giving server name alone you will get connected to default instance of the server.

Examples:

SSMS -S (local) –E
– connect to default instance using Windows Authentication

SSMS -S (local)\InstanceName –E
– connect to named instance using Windows Authentication

SSMS -S (local) -U sa -P P@$$w0rD
– connect to default instance using SQL Server Authentication

SSMS -S (local) -E -U sa -P P@$$w0rD
– connect to default instance using SQL Server Authentication– SQL Server Authentication takes precedence

SSMS -S (local) -E C:\MyQuery.sql– connect to default instance using Windows Authentication– and open a saved query file

SSMS -S (local) -d AdventureWorks2008R2 -E– connect to default instance using Windows Authentication– and change context to AdventureWorks2008R2 database

All the options described above are for SQL Server 2008. The same options can be used with SQL Server 2005. However, for SQL Server 2005, replace SSMS.exe with SqlWb.exe.

Denali's New features conslidated

“Denali” introduces Ad-hoc query paging in which you can specify range of rows returned by a SELECT statement. This can be handy when you want to limit number of returned by the statement.
This is implemented in ORDER BY clause. two new keywords are added to ORDER BY clause:

1. OFFSET : Skips top N number of rows from the result set
2. FETCH : Fetch next N number of rows from the result set (after skipping rows specified by OFFSET)

Let’s see it in action:

1. This will remove first 5 rows from the result:
– Remove First 5 Rows from the Result Set
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ProductID
OFFSET 5 ROWS

2. Remove first 5 rows from the result, and return next 10 rows:
– Remove First 5 Rows from the Result Set,
– Return Next 10 Rows
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ProductID
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY

3. Return bottom 5 rows only:
– Return Bottom 5 Rows
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ProductID DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY

SQL TIPS / Work Arounds

SCENARIO 1:

If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.
However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:

Msg 4405, Level 16, State 1, Line 1
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.

To avoid this error and make a view modifiable you need to create Triggers on the view. These triggers will be used to ‘pass’ the changes to base tables.

You need to create a trigger on a view for all operations you need to perform. For example, if you need to perform INSERT operations on a view you need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables. If you also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.

SCENARIO 2:

How to check on how long the SQL server is running for ?

One way is to check the SQL Server Logs and the other way and Another quick way is to check the creation time of  “tempdb” database. This will be same as SQL Server Service start time since “tempdb” is recreated each time SQL Server starts.

SELECT create_date

FROM   sys.databases

WHERE  name = ‘tempdb’

OR in a formatted way:

SELECT (DATEDIFF(DAY, create_date, GETDATE()))

       AS [Days],

       ((DATEDIFF(MINUTE, create_date, GETDATE())/60)%24)

       AS [Hours],

       DATEDIFF(MINUTE, create_date, GETDATE())%60

       AS [Minutes]

FROM   sys.databases

WHERE  name = ‘tempdb’

How To Get Recently Executed Queries

Using DMV & DMF's:

SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > '2011-05-06 19:15:00.000'
ORDER BY      STATS.last_execution_time DESC

Q's on Differences in SQL Server

SUB-QUERY VS INNER JOIN:

SUB-QUERY:
SELECT * FROM TABLEA WHERE  ID IN ( SELECT ID FROM TABLEB)

INNER JOIN:
SELECT * FROM TABLEA  X
        INNER JOIN TABLEB  Y  ON X.ID  = Y.ID  AND X.NAME = Y.NAME

Using sub-queries we can join based only on one column where as using joins we can use more than one.

STORED PROCEDURE VS FUNCTION:

SP:

1. Pre-Complied.
2. Proceeds execution till the last statement even if any error occurs in the middle.
3. Providing an output is not a mandatory.
4. Can Provide multiple outputs with the help of OUTPUT keyword.
5. Cannot be called in a select statement.
6. Can call other SP's or Functions inside it.
7. Reduces network traffic by executing bunch of statements together.

Functions:

1. Can be called in a select statement.
2. Should mandatorily provide the output.
3. Cannot call SP's inside it.
4. User Defined Functions cannot be used to modify base table information.
5. SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions.GETDATE is an example of a non-deterministic function.
6. Can be used in joins.

UNION VS UNION ALL

Union gives the distinct common records between the entities. where are Union all brings all the records from the two entities but in common the attributes used in the select clause should hold the same data type in both the tables.

VIEW VS MATERIALIZED VIEW

Views do not store data physically where the materialized view does. The Data snapshot inside materialized view resides till the next call to it and if there is no modifications in the underlying data. If any changes made to base data gets reflected in materialized view on the next call to it.

LEN() Vs DATALENGTH()

While LEN() is used to return the number of characters in a String, DATALENGTH() can be used to return the number of bytes used by any expression.

Example:

CREATE TABLE dbo.SampleTable
(
      NameNVarchar      NVARCHAR(64),
      NameVarchar       VARCHAR(64),
      NameChar          CHAR(64)
)

INSERT INTO dbo.SampleTableVALUES
('Anitha Saradhi', 'Anitha Saradhi', 'Anitha Saradhi')
INSERT INTO dbo.SampleTableVALUES
('Nidhi', 'Nidhi', 'Nidhi')

SELECT      LEN(NameVarchar)  AS 'LEN(VARCHAR)',
            LEN(NameNVarchar) AS 'LEN(NVARCHAR)',
            LEN(NameChar)     AS 'LEN(CHAR)'
FROM        dbo.SampleTable

SELECT      DATALENGTH(NameVarchar)  AS 'DATALENGTH(VARCHAR)',
            DATALENGTH(NameNVarchar) AS 'DATALENGTH(NVARCHAR)',
            DATALENGTH(NameChar)     AS 'DATALENGTH(CHAR)'
FROM        dbo.SampleTable

Results:

LEN(VARCHAR) LEN(NVARCHAR) LEN(CHAR)
------------ ------------- -----------
14           14            14
5            5             5
(2 row(s) affected)

DATALENGTH(VARCHAR) DATALENGTH(NVARCHAR) DATALENGTH(CHAR)
------------------- -------------------- ----------------
14                  28                   64
5                   10                   64
(2 row(s) affected)

Types Of Functions

Types of User Defined Functions

There are three different types of User Defined Functions. Each type refers to the data being returned by the function. Scalar functions return a single value. In Line Table functions return a single table variable that was created by a select statement. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required.

Scalar UDFs

Our first User Defined Function will accept a date time, and return only the date portion. Scalar functions return a value. From inside Query Analyzer, enter:
 

CREATE FUNCTION dbo.DateOnly(@InDateTime datetime)

RETURNS varchar(10)

AS

BEGIN

        DECLARE @MyOutput varchar(10)

        SET @MyOutput = CONVERT(varchar(10),@InDateTime,101)

        RETURN @MyOutput

END

To call our function, execute: SELECT dbo.DateOnly(GETDATE())
Notice the User Defined Function must be prefaced with the owner name, DBO in this case. In addition, GETDATE can be used as the input parameter, but could not be used inside the function itself. Other built in SQL functions that cannot be used inside a User Defined Function include: RAND, NEWID, @@CONNCECTIONS, @@TIMETICKS, and @@PACK_SENT. Any built in function that is non-deterministic.
The statement begins by supplying a function name and input parameter list. In this case, a date time value will be passed in. The next line defines the type of data the UDF will return. Between the BEGIN and END block is the statement code. Declaring the output variable was for clarity only. This function should be shortened to:
 

CREATE FUNCTION testDateOnly(@InDateTime datetime)

RETURNS varchar(10)

AS

BEGIN

        RETURN CONVERT(varchar(10),@InDateTime,101)   

END

Inline Table UDFs

These User Defined Functions return a table variable that was created by a single select statement. Almost like a simply constructed non-updatable view, but having the benefit of accepting input parameters.
This next function looks all the employees in the pubs database that start with a letter that is passed in as a parameter. In Query Analyzer, enter and run:
 

USE pubs

GO

CREATE FUNCTION dbo.LookByFName(@FirstLetter char(1))

RETURNS TABLE

AS

RETURN SELECT *

FROM employee

WHERE LEFT(fname, 1) =  @FirstLetter

To use the new function, enter:

SELECT * FROM dbo.LookByFName('A')

All the rows having a first name starting with A were returned. The return is a Table Variable, not to be confused with a temporary table. Table variables are new in SQL 2000. They are a special data type whose scope is limited to the process that declared it. Table variables are stated to have performance benefits over temporary tables. None of my personal testing has found this result though.

Multi Statement UDFs

Multi Statement User Defined Functions are very similar to Stored Procedures. They both allow complex logic to take place inside the function. There are a number of restrictions unique to functions though. The Multi Statement UDF will always return a table variable–and only one table variable. There is no way to return multiple result sets. In addition, a User Defined Function cannot call a Stored Procedure from inside itself. They also cannot execute dynamic SQL. Remember also, that UDFs cannot use non-deterministic built in functions. So GETDATE and RAND cannot be used. Error handling is restricted. RAISERROR and @@ERROR are invalid from inside User Defined Functions. Like other programming languages, the purpose of a User Defined Function is to create a stand-alone code module to be reused over and over by the global application.
For a Multi Statement test, we will create a modified version of the LookByFName function. This new function will accept the same input parameter. But rather than return a table from a simple select, a specific table will be created, and data in it will be manipulated prior to the return:

CREATE FUNCTION dbo.multi_test(@FirstLetter char(1))

RETURNS @Result TABLE

        (

        fname varchar(20),

        hire_date datetime,

        on_probation char(1)

        )

AS

BEGIN

        INSERT INTO @Result

               (fname, hire_date)

               SELECT fname, hire_date

               FROM employee

               WHERE LEFT(fname, 1) =  @FirstLetter

        UPDATE @Result

        SET on_probation = 'N'

        UPDATE @Result

        SET on_probation = 'Y'

        WHERE hire_date < '01/01/1991'

        RETURN

END

To use the new function, execute:

SELECT * FROM dbo.multi_test('A')

With the new Multi Statement Function, we can manipulate data like a Stored Procedure, but use it in statement areas like a View.
For example, only specific columns can be returned.

SELECT fname FROM dbo.multi_test('A')

The function can also be joined like a view:

SELECT e.lname, f.fname

FROM employee e INNER JOIN dbo.multi_test('A') f ON e.fname = f.fname

CASE Samples

Simple CASE Syntax: CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Example:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO


Using Select with Searched Case:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Name, 'Price Range' =
      CASE
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO


As a Replacement for IIF:

SELECT FirstName, LastName, TelephoneNumber,
     IIf(IsNull(TelephoneInstructions),"Any time",
     TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;

Using Case in Order By:

ECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;


Using Case in Update statement:

USE AdventureWorks2008R2;
GO
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

Using Case in SET Statement:

SET @ContactType =
        CASE
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
                WHERE e.BusinessEntityID = @BusinessEntityID)
                THEN 'Employee'


            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec
                WHERE bec.BusinessEntityID = @BusinessEntityID)
                THEN 'Vendor'


            -- Check for store
            WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v         
                WHERE v.BusinessEntityID = @BusinessEntityID)
                THEN 'Store Contact'


            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Customer AS c
                WHERE c.PersonID = @BusinessEntityID)
                THEN 'Consumer'
        END;


Using Case in Having Clause:

USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
        THEN ph1.Rate
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F'
        THEN ph1.Rate 
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

Transaction Flow in Sql Server 2005

Every transaction moves through the various checks and code in the following order:

1. Identity insert check.
2. Nullability constraint.
3. Data-type check.
4. Instead of trigger execution. If an instead of trigger exists, execution of the DML
stops here. Instead of triggers are not recursive. Therefore, if the insert trigger executes
a DML command that fires the same event (insert, update or delete), then the
instead of trigger will be ignored the second time around.
5. Primary-key constraint.
6. Check constraints.
7. Foreign-key constraint.
8. DML execution and update to the transaction log.
9. After trigger execution.
10. Commit transaction.
11. Writing the data file.

Counts or Limit

SQL SERVER 2008 R2:

SQL Server Database Engine object	Maximum sizes/ numbers SQL Server (32-bit)	Maximum sizes/ numbers SQL Server (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8,000	8,000
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8,060	8,060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per nonwide table	1,024	1,024
Columns per wide table	30,000	30,000
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	4096	4096
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server editions.	50 instances on a stand-alone server.
	SQL Server supports 25 instances on a failover cluster.	25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	999	999
Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP	32	32
Number of grouping sets generated by operators in the GROUP BY clause	4,096	4,096
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1,000	1,000
Statistics on non-indexed columns	30,000	30,000
Tables per SELECT statement	Limited only by available resources	Limited only by available resources
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables)	4096	4096
User connections	32,767	32,767
XML indexes	249	249

SQL Server Utility object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Computers (physical computers or virtual machines) per SQL Server Utility	100	100
Instances of SQL Server per computer	5	5
Total number of instances of SQL Server per SQL Server Utility	2001	2001
User databases per instance of SQL Server, including data-tier applications	50	50
Total number of user databases per SQL Server Utility	1000	1000
File groups per database	1	1
Data files per file group	1	1
Log files per database	1	1
Volumes per computer	3	3

SQL Server Replication object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Articles (merge publication)	256	256
Articles (snapshot or transactional publication)	32767	32767
Columns in a table1 (merge publication)	246	246
Columns in a table2 (SQL Server snapshot or transactional publication)	1000	1000
Columns in a table2 (Oracle snapshot or transactional publication)	995	995
Bytes for a column used in a row filter (merge publication)	1024	1024
Bytes for a column used in a row filter (snapshot or transactional publication)	8000	8000

2005:

SQL Server 2005 Database Engine object	Maximum sizes/numbers SQL Server 2005 (32-bit)	Maximum sizes/numbers SQL Server 2005 (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8000	8000
Bytes per GROUP BY, ORDER BY	8060	8060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8060	8060
Bytes per varchar(max),varbinary(max), xml, text, or imagecolumn	2^31-1	2^31-1
Characters per ntext or nvarchar(max)column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per base table	1024	1024
Columns per SELECT statement	4096	4096
Columns per INSERT statement	1024	1024
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,258 terabytes	524,258 terabytes
Databases per instance of SQL Server	32767	32767
Filegroups per database	32767	32767
Files per database	32767	32767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server 2005 editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances.	50 instances on a stand-alone server.
	SQL Server 2005 supports 25 instances on a failover cluster.	25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	249	249
Parameters per stored procedure	2100	2100
Parameters per user-defined function	2100	2100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1000	1000
Statistics on non-indexed columns	2000	2000
Tables per SELECT statement	256	256
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered
User connections	32767	32767
XML indexes	249	249

SQL Server 2005 Replication object	Maximum sizes/numbers SQL Server 2005 (32-bit)	Maximum sizes/numbers SQL Server 2005 (64-bit)
Articles (merge publication)	256	256
Articles (snapshot or transactional publication)	32767	32767
Columns in a table1 (merge publication)	246	246
Columns in a table2 (SQL Server snapshot or transactional publication)	1000	1000
Columns in a table2 (Oracle snapshot or transactional publication)	995	995
Bytes for a column used in a row filter (merge publication)	1024	1024
Bytes for a column used in a row filter (snapshot or transactional publication)	8000	8000