:::: MENU ::::

Posts Categorized / Data Types

  • Feb 29 / 2012
  • 0
Data Modeling and Database Design, Data Types, dbDigger, DDL, SQL Server Error messages

Table creation failed due to row size limit

Following error messages may be generated while trying to create index on table having row size greater than 8060 bytes. In case where index is also tried to create following error would be generated
Index ‘%’ row length exceeds the maximum permissible length of ‘8060’ bytes.

And while trying to create table with out index and size greater than 8060 bytes, following error would be generated
Creating or altering table ‘%’ failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

SQL Server extent size is 8060 bytes and is comprised of 8 pages. Individual table row under 8060 bytes size is requirement of table creation. However data types like image or text would not be considered as participant in max size. If row size is expected to cross this limit then error message would be generated and creation would be failed.

Work Around

Some work around may be applied to solve the issue.

  • Try to cut down the number of columns by normalization
  • Try to optimize the data types
  • Study feasibility to change columns like char(8000) columns with text data type

Following are some scenarios where error messages related to row max size may be generated

-- Row size error message without index   
 IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
 DROP TABLE tblMaxRowDemo  
 GO  
 CREATE TABLE tblMaxRowDemo  
 (ID INT IDENTITY(1,1),  
 col1 CHAR(6000),col2 CHAR(6000))  
 GO  
 -- Row size error message with index   
 IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
 DROP TABLE tblMaxRowDemo  
 GO  
 CREATE TABLE tblMaxRowDemo  
 (ID INT PRIMARY KEY IDENTITY(1,1),  
 col1 CHAR(6000),col2 CHAR(6000))  
 GO  
  • Aug 24 / 2010
  • 0
Data Types, dbDigger, T-SQL Interview Questions

Purpose of using Varchar(max)

varchar and nvarchar may store maximum bytes up to 8000 and 4000 respectively. In SQL Server 2005 and onwards we have another parameter max for varchar or nvarchar as field length. It can be used as varchar(max) or nvarchar(max).
Actually it is better version of text, ntext and image data types of SQL Server 2000. It enables the DBMS to store about 2 GB of data in a comparable data type. You can hold this value in a variable and also can apply comparison and manipulation functions on this huge amount of data.
There were a lot of limitations while using text, ntext and image data types. These values do not allow to use several of string functions. But also be aware of the fact that this tremendous power of using such large data as a string may eat your resources. So use this power with care.>
So you should be using varchar(n) or nvarchar(n) for bytes up to 8000 or 4000 respectively. And for larger text options consider using max as varchar(max) or nvarchar(max).

  • Jan 07 / 2010
  • 0
Data Modeling and Database Design, Data Types, DBA Interview questions, dbDigger, T-SQL Interview Questions

Find columns with identity property

Question:

In SQL Server 2005, how can you easily determine which columns have the identity property set?

Answer:

  • Query sys.identity_columns for the rows.
  • Query sys.columns.is_identity for a value of 1

Explanation: There is a table, sys.identity_columns that contains a row for each column in your database that has the identity property set. There is also a column called is_identity in the sys.columns view that contains a 1 if the column has the identity property set.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Aug 22 / 2009
  • 1
Data Modeling and Database Design, Data Types, DBA Interview questions, dbDigger, T-SQL Interview Questions

Using identity columns or UniqueIdentifier for auto generated keys

The most commonly used options for key auto generation in SQL Server are

  • Using identity columns
  • Using unique key identifiers GUID

There are some differences to consider between both ways.
In case of identity columns

  • Identity columns are auto generated with each record
  • Identity columns provide uniqueness for records in a single table in which identity column is defined
  • Identity columns consume space according to data type. For example identity column defined over integer data type would consume 4 bytes

In case of Unique Identifier (GUID)

  • GUID provide uniqueness on platform. They may be generated by combination of MAC address and CPU clock.
  • GUID is required to generate for each record through NEWSEQUENTIALID() or newID() function. NEWSEQUENTIALID() GUID in serial while newID() generates random GUID.
  • GUID may consume 16 bytes of storage space on disk. It is relatively much more than 4 bytes of integer in identity column case.

So conclusion is that identity columns are efficient and suitable while working in local databases. And you have to look for GUID while working with consolidated data or data replication.
Consult BOL for further help on using GUID

  • Jul 20 / 2009
  • 0
Data Modeling and Database Design, Data Types, dbDigger, T-SQL Scripts

Analyze all identity column values in SQL Server database

Identity columns are created to auto generate keys for records. These keys are best suitable for primary keys. If any value crosses the max value for identity column data type then following over flow error may occur, as here in case of tiny int

Msg 8115, Level 16, State 1, Line 5
Arithmetic overflow error converting IDENTITY to data type tinyint.

Here is a script to generate list of all identity columns in database along with their data types and maximum value consumed

 
Use AdventureWorks  
 GO  
 SELECT  
 SCHEMA_NAME( OBJECTPROPERTY( c.OBJECT_ID, 'SCHEMAID' )) AS [SCHEMA NAME],  
 OBJECT_NAME( c.OBJECT_ID ) AS [TABLE NAME], c.NAME AS [COLUMN NAME],  
 t.name as [Column data Type],seed_value,  
 increment_value,last_value as CurrentMaxValue  
 FROM SYS.identity_COLUMNS c inner join sys.types t on c.user_type_id = t.user_type_id  
 WHERE COLUMNPROPERTY(OBJECT_ID, c.NAME, 'IsIdentity') = 1  
 AND last_value IS NOT NULL  
 order by last_Value Desc  
 GO  

Analyze identity columns in SQL Server database
Now you may analyze the list of identity columns. And any column that is near to fully consume its maximum value may easily be tracked here.

  • Jun 25 / 2009
  • 0
Data Types, dbDigger

In EXECUTE, procname can only be a literal or variable of type char, varchar, nchar, or nvarchar.

Following error Message
Msg 8199, Level 16, State 1, Line 3
In EXECUTE , procname can only be a literal or variable of type char, varchar, nchar, or nvarchar.

can occur on all versions of SQL Server. This error message appears when you try to EXECUTE a variable that is not of one of the types listed in the error message. To resolve the error, the variable to be executed must be of a type listed in the error message.

Following is an example to generate the error

DECLARE @i INT
SET @i = 1
EXECUTE @i

Remarks:
In the above error message we try to execute a variable of the type INT. This raises the error.

Pages:123
Consult us to explore the Databases. Contact us