:::: MENU ::::

Posts Categorized / Data Modeling and Database Design

  • Feb 01 / 2010
  • 0
Data Modeling and Database Design, dbDigger, Identity Columns, T-SQL Interview Questions, T-SQL Tips and Tricks

Insert value in identity column when identity column is only column in table

I came across a question that how to insert value in identity column when identity column is only column in the table. And to set identity insert on is not allowed. It is interesting scenario. So answer is to use DEFAULT VALUES. Consider following code for demo that how to insert value in identity column when identity column is only column in table.

USE AdventureWorks  
GO  
CREATE TABLE #TestInsert(onlyColum TINYINT IDENTITY (1,1))  
GO  

INSERT INTO #TestInsert DEFAULT VALUES 
GO  

SELECT * FROM #TestInsert  
GO 

And you would get inserted value from this temporary table.

  • 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

  • Aug 17 / 2009
  • 0
Data Modeling and Database Design, Database Diagrams, dbDigger, Publications of Atif Shehzad on MSSQLTips.com, SSMS tips and tricks

Getting started with SQL Server database diagrams

My article related to Getting started with SQL Server database diagrams is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Getting started with SQL Server database diagrams. It would be very helpful to start effectively working with Database Diagrams in SSMS.

  • 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.

  • Jul 20 / 2009
  • 0
Data Modeling and Database Design, dbDigger, SQL Server Collations, T-SQL Interview Questions

Change Collation of a databse through T-SQL

In a previous post i discussed that how to get list of SQL Server collations. Now we have to see that how to change collation o a database.
Before completion of this task make sure that you have no column value that depends upon collation settings.
After that get name of required collation that you are going to implement. List of collations can be retrieved from our previous article. In this example we will change collation of database AdventureWorks to ‘SQL_Latin1_General_CP1_CI_AS’

 
Alter Database AdventureWorks  
 Collate SQL_Latin1_General_CP1_CI_AS  
 GO  

After that a success message will appear and collation is now changed.

Consult us to explore the Databases. Contact us