My article related to Using SET or SELECT for assigning values to variable is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Using SET or SELECT for assigning values to variable.
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
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
After that a success message will appear and collation is now changed.
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
(SELECT col1, col2, col3
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
Where col1, col2, col3 are columns on which duplicate rows are determined in table1.
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.
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.