small bug in SQL server 2005 developer edition with SP 2

Recently SQL server 2008 also release but SQL server 2005 I notice there has small bug but it’s not the big issue, but SQL server QA team need to find out those things. This error I notice SQL server Developer edition when you create a backup database using graphical view (Not the T-SQL) after finish database backup it show Executing (0 %) I see this error twice normally I’m taking backups 5 or 6 time per day. And I was install SQL server 2005 developer edition with SP 2,

Minimum year value in SQL server 2008(date data type)

Minimum year value in SQL server 2008(date data type)

SQL server 2008 comes with lots of new features and they separated SQL server datetime data type as Date and Time (datetime data type also available).SQL server previous versions (SQL 2000, SQL 2005) there is limit for min date (year-1753) and max date (year-9999). But when you notice SQL server 2008 date data type min date is 0001.

/* Date 24/06/2008 SQL SERVER 2000 and 2005 */ DROP TABLE dateCheck; CREATE TABLE dateCheck(dateCol datetime); GO --can INSERT INTO dateCheck(dateCol) VALUES ('12/31/1753') GO --can't INSERT INTO dateCheck(dateCol) VALUES ('01/01/1752') GO SELECT * FROM dateCheck GO /* Date 24/06/2008 SQL SERVER 2008 */ --datetime data type DROP TABLE dateCheckDateTime; CREATE TABLE dateCheck(dateCol datetime); GO --can INSERT INTO dateCheck(dateCol) VALUES ('12/31/1753'); --can't INSERT INTO dateCheck(dateCol) VALUES ('01/01/1752'); GO SELECT * FROM dateCheck; GO --date data type DROP TABLE dateCheckDate; CREATE TABLE dateCheckDate(dateCol date); GO --can INSERT INTO dateCheckDate(dateCol) VALUES ('01/01/1753'); INSERT INTO dateCheckDate(dateCol) VALUES ('01/01/1752'); INSERT INTO dateCheckDate(dateCol) VALUES ('01/01/0001'); GO SELECT * FROM dateCheckDate; GO

SQL SERVER 2005 - MSXML6 SETUP Fail

SQL SERVER 2005 - MSXML6 SETUP Fail

MSXML6 is the latest MSXML product from Microsoft, and is contain with SQL Server 2005, Visual Studio 2005, .NET Framework (3.0,3.5) , Windows Vista and Windows XP Service Pack 3.When you going to install SQL server 2005, some certain situation you can notice MSXML6 setup fail to install and all setup program will be fail. And when you going to run individual setup files, under SQL server 2005 setup folder (“\ Tools\Setup\msxml6.msi”) it will gives you following error “The system administrator has set policies to prevent this installation”

MS says about this http://support.microsoft.com/kb/928469

Solution;

1)Go to add remove programs and remove MSXML 6.0 Parser if available
2)Then removing few registry values you can sort out this problem.
HKEY_CLASSES_ROOT\Installer\Products\
Go to above path and check product name call “MSXML 6.0 Parser” and remove all the related values and reinstall SQL server 2005 setup.
Related links,
http://blogs.msdn.com/quanto/archive/2005/06/29/434136.aspx

Problem to create an index on a BIT (data type) column (SQL server 2000)

When we try to create on an index to Bit data type column using previous versions (SQL Server 2000) of SQL server it doesn’t display the bit data type columns.

And when we type columns name manually it show the message box “Do you want to delete this index?” then how we create index to bit data type column using SQL server 2000?bit when you notice, to create index using manage indexes property (all task > manage indexes) we can create index to bit data type columns with out any problem.



















And also Good new is SQL server 2005 version you can create an index to bit data type column with out any problem.

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)