Hi
1.Maximum columns in a Table:
DECLARE @intSerial INT,
@strSQL NVARCHAR(MAX)
SELECT @intSerial = 1
SELECT @strSQL = 'CREATE TABLE #TBLSAMPLE1(ID INT IDENTITY,'
WHILE (@intSerial <= 1024)
BEGIN
SELECT @strSQL = @strSQL + 'COL' + CAST(@intSerial AS VARCHAR) + ' VARCHAR(10),'
SELECT @intSerial = @intSerial + 1
END
SELECT @strSQL = @strSQL + ')'
EXEC SP_EXECUTESQL @strSQL
Error Message :
Msg 1702, Level 16, State 1, Line 1
CREATE TABLE failed because column 'COL1024' in table '#TBLSAMPLE1' exceeds the maximum of 1024 columns.
2.Maximum size of a Row:
1. According to MS specification, Maximum size of a Row in table is :
8060 Byte(s).
2. But it will allow only
8052 Byte(s)
3. What about the remaining
8 Byte(s)?
Normally, 8 Byte(s) reserved by SQL Server for internal reference. The 8 byte(s) will be used for Header level information. So, the remaining 8052 Byte(s) only we can use.
Total Bytes : 8060 Byte(s)
Reserved : 8 Byte(s)
Remaining : 8052 Byte(s)
So, we can use 8052 byte(s) only. See the following sample..
CREATE TABLE TBL_MAX_ROWS
(
COL1 CHAR(1000),
COL2 CHAR(1000),
COL3 CHAR(1000),
COL4 CHAR(1000),
COL5 CHAR(1000),
COL6 CHAR(1000),
COL7 CHAR(1000),
COL8 CHAR(1000),
COL9 CHAR(60)
)
Error Message :
Msg 1701, Level 16, State 1, Line 2
Creating or altering table 'TBL_MAX_ROWS' failed because the minimum row size would be 8068, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
But you can use 8052 byte(s)
CREATE TABLE TBL_MAX_ROWS
(
COL1 CHAR(1000),
COL2 CHAR(1000),
COL3 CHAR(1000),
COL4 CHAR(1000),
COL5 CHAR(1000),
COL6 CHAR(1000),
COL7 CHAR(1000),
COL8 CHAR(1000),
COL9 CHAR(52)
)
Command(s) completed successfully.
Cheers
Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions
Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator