:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

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

  • Jun 22 / 2009
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to get duplicate rows from a table

In one of my previous articles, i discussed that how to remove duplicate rows from a table. While analyzing the duplicate rows based on some columns, often it is required to get whole list of columns even if they are not included in our duplicate criteria.
Instead of creating a table and populating it with data, i will simply go through the concept through a sample script only to get the list.

 
SELECT --comma separated list of all columns to select   
 FROM table1 a  
 join   
 (SELECT col1, col2, col3   
 FROM table1  
 GROUP BY col1, col2, col3   
 HAVING count(*) > 1) b   
 ON a.col1 = b.col1  
 and a.col2 = b.col2  
 and a.col3 = b.col3  
 ORDER BY a.col1, a.col2, a.col3  
 GO  

Where col1, col2, col3 are columns on which duplicate rows are determined in table1.

  • Jun 17 / 2009
  • 0
Data Types, dbDigger, T-SQL Interview Questions

Cannot update a timestamp column

The error message

Msg 272, Level 16, State 1, Line 4
Cannot update a timestamp column.

is a run time error message and appears when you try to update a table column of type TIMESTAMP. Values in a column of type TIMESTAMP can not be modified with an UPDATE statement. These values are generated and modified by SQL Server. This error can occur on all versions of SQL Server.
The data type TIMESTAMP has nothing to do with DATE, TIME, or a combination of both values. Values of type TIMESTAMP are binary numbers. The synonym ROWVERSION is a much clearer description for this data type.

  • Jun 09 / 2009
  • 0
dbDigger, T-SQL Interview Questions

An aggregate may not appear in the OUTPUT clause

The following Error Message is a run time error message introduced in SQL Server 2005.

Msg 158, Level 15, State 1, Line 2
An aggregate may not appear in the OUTPUT clause.

This error message appears when you try to use an aggregate function in the OUTPUT clause of an UPDATE statement. Aggregate functions are not allowed in the OUTPUT clause. For smooth execution remove any used aggregate function from out put clause.

  • May 07 / 2009
  • 0
dbDigger, T-SQL Interview Questions

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

The error message
Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
is a run time error message of severity level 15. This error message appears when you try to call any view, function, derived table or subquery without also specifying TOP or FOR XML. To resolve the error message add the TOP or FOR XML clause to the statement

Consult us to explore the Databases. Contact us