Sunday, February 24, 2008

ISNULL changes to NOT to allow NULLs

Hi folks. Actually, it is pretty old trick/feature I would like to show you. Let's say we have a table called 'Test1' with one column as VARCHAR(10) which allows inserting NULLs. Well,we want to change this to NOT allow NULLs and I'm also going to change datatype for this column at the same time ,so just for testing let's assume that 'Test1' does not contain alphabetic values.


CREATE TABLE Test1
(
Col1 varchar(10) NOT NULL,
)
INSERT INTO Test1 VALUES('1')

EXEC sp_rename 'Test1', 'Test1_Old'
GO
SELECT ISNULL(CAST(Col1 AS int), 0) AS Col1 ---Makes Col1 not to allow NULLs
INTO Test1
FROM Test1_Old

DROP TABLE Test1_Old

Make sure that you do not have duplicates, so you can create a constraint on the column.
/*
ALTER TABLE Test1
ADD CONSTRAINT PK_Test1
PRIMARY KEY (Col1)
*/
GO

Tuesday, February 12, 2008

Percent of completion for some operations in SS2005

Hi folks
I have seen many and many businesses today have already upgraded to SQL Server 2005 and at this time I would like to share with you some of new features that this product gives us.
As you know SQL Server 2005 provides lots of dynamic management views (DMV) to get access to internal behaviour of processes or commands. If I remember well, someone of MVPs has already published about this feature , so anywhere..

Take a look at this script. Run for example BACKUP DATABASE comand and in another session run the below script. It provides you with start time column , percent of complete and what possible completion time is...(estimated_completion_time ) COOL, right?.

SELECT TOP 2 start_time,
percent_complete ,estimated_completion_time
FROM sys.dm_exec_requests
ORDER BY start_time desc

Check it out with those commands

DBCC CHECKDB
DBCC SHRINKDATABASE
DBCC SHRINKFILE
BACKUP DATABASE
ROLLBACK