NULL values and WITH ROLLUP

When you using WITH ROLLUP, you can get group total and full total within the result set but imagine when you allow null columns and writing T-SQL statement with using WITH ROLLUP for groups by your GROUP BY columns? It calculate correct values but we can’t sort it out.

USE mydb GO DROP TABLE ITEM GO CREATE TABLE ITEM ( categoryCode varchar(10) NOT NULL, itemCode varchar(10)NOT NULL, qty int NOT NULL ) GO INSERT INTO ITEM VALUES('category 1','item 1',5) INSERT INTO ITEM VALUES('category 1','item 2',4) INSERT INTO ITEM VALUES('category 1','item 3',2) INSERT INTO ITEM VALUES('category 2','item 1',12) INSERT INTO ITEM VALUES('category 2','item 2',8) INSERT INTO ITEM VALUES('category 3','item 2',1) GO SELECT isnull(categoryCode,' Full') AS categoryCode, isnull(itemCode,' Total is ') AS itemCode, sum(qty ) totQty FROM ITEM GROUP BY categoryCode,itemCode WITH ROLLUP GO --After setting allow nulls ALTER TABLE ITEM ALTER COLUMN categoryCode varchar(10) NULL ALTER TABLE ITEM ALTER COLUMN itemCode varchar(10) NULL GO INSERT INTO ITEM VALUES('category 1',null,5) INSERT INTO ITEM VALUES('category 2',null,4) INSERT INTO ITEM VALUES(null,'item 3',2) GO SELECT isnull(categoryCode,' Full') AS categoryCode, isnull(itemCode,' Total is ') AS itemCode, sum(qty ) totQty FROM ITEM GROUP BY categoryCode,itemCode WITH ROLLUP GO

timestamp Data type makes my big mistake

Oh…timestamp Data type makes my big mistake
Few days a go I was added post about timestamp data type but unfortunately it was not the correct way. And the name timestamp data type is a little misleading because timestamp data type has nothing to do with date and time and this is not possible converted to date time data type. We have to notice this, The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL date time data type. (SQL Server Books Online)

SQL Server Books Online Say’s:

timestamp The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time. In SQL Server version 7.0 and SQL Server 2000, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one. Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified. To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.

But I have little doubts about CAST or CONVERT function chart its shows as timestamp allow converting (Implicit conversions) to date time.

Avoid Using TOP Clause much as possible in SQL server

Most of the time we need to return number of rows (first two rows or first five rows) in the table, then normally we are applying TOP clause. Then it can not be a performance issue or time consuming problem because it will takes only few (one or less) milliseconds.

Syntax;

[ TOP (expression) [PERCENT] [ WITH TIES ] ]

Examples;

USE AdventureWorks ; GO DECLARE @p AS int SET @p='10' SELECT TOP(@p)* FROM HumanResources.Employee; GO

(Books Online – Search for TOP Clause)

But when you use very large table (Ex: more than 1000000) and need to return first 10000 records with using top clause then it will take much time and it can be a performance issue. In that case I am using following method to return first ten thousand records,

USE AdventureWorks ; GO DECLARE @intNum AS int SET @intNum =10000 SET ROWCOUNT @intNum; SELECT * FROM Sales.SalesOrderDetail ORDER BY UnitPrice,SalesOrderID SET ROWCOUNT 0 GO

Using Rowcount option we can avoid top clause but there is have some limitations, when we applying Rowcount option.
i)Inside the view we can’t use Rowcount option.
ii)Using Rowcount we can’t create sub query’s (Ex. SELECT TMP.SalesOrderID,TMP.UnitPrice FROM (SELECT TOP 10000 * FROM Sales.SalesOrderDetail
ORDER BY UnitPrice,SalesOrderID) TMP)