TODO:
Have you ever wanted a function that tells you if a certain date is a Weekday?
SOLUTION:
CREATE FUNCTION IsWeekday(@InputDate datetime) RETURNS int
AS
BEGIN
DECLARE @isweekday int
select @isweekday=choose(datepart(dw, @InputDate), 0,1,1,1,1,1,0)
RETURN @isweekday
END
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to perform a join during an Update using T-SQL?
SOLUTION:
UPDATE
Person
SET
Person.Name = PersonArchive.Name
FROM
Person
INNER JOIN
PersonArchive
ON
Person.Id = PersonArchive.Id
NOTES:
You can also add a WHERE clause as to not update all records.
TODO:
Have you ever wanted to use a CASE statement in your T-SQL Select Query?
SOLUTION:
SELECT Column =
CASE
WHEN SomeValue = 0 THEN 'Zero'
WHEN SomeValue < 0 THEN 'Minus'
ELSE 'Plus'
END
FROM MyTable
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to shrink a large SQL Server database in small increments?
SOLUTION:
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink
set @DBFileName = 'MyDB'
-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000
-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 100
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin
set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
exec ( @sql )
if @@ERROR <> 0
begin
print @@ERROR
break
end
print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
end
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to get a comma separated list of columns that belong to a table using T-Sql?
SOLUTION:
DECLARE @columnList varchar(8000)
DECLARE @name varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY COLUMN_NAME ASC;
SET @columnList=''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @columnList=''
SET @columnList = '[' + @name + ']'
ELSE
SET @columnList = @columnList + ',[' + @name + ']'
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT @columnList
NOTES:
Just paste to a query window, hit ctrl-a and F5 to run it.
TODO:
Have you ever wanted to create an index using T-SQL. Below is simple syntax to do just that.
SOLUTION:
CREATE INDEX idx_SomeTable_SomeId ON SomeTable(Some_Id)
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to update a column to have a default constraint?
SOLUTION:
ALTER TABLE MyTable ADD CONSTRAINT DF_MyTable_IsTest DEFAULT 0 FOR IsTest
NOTES:
The T-SQL above, will add a default constraint, that will default the column 'IsTest' to 0
TODO:
Have you ever wanted to use a CASE statement in a SELECT statement in T-SQL?
SOLUTION:
SELECT Id,
CASE PrimaryColor WHEN NULL THEN
HeaderColor
ELSE
PrimaryColor
END AS HeaderColor
,SectionHeader...
FROM...
WHERE...
NOTES:
This example will evaluate the column 'PrimaryColor'. If it is NULL, then the column 'HeaderColor' will be used. If it does have a value, then 'PrimaryColor' will be used.
TODO:
Have you ever wanted to drop a column to an existing SQL Server Table using T-SQL?
SOLUTION:
ALTER TABLE Customer DROP COLUMN FavoriteColor
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to get badly fragmented indexes, and rebuild them using T-SQL?
SOLUTION:
CREATE procedure [dbo].[sp_GetIndexFragmentation]
AS
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
--UNCOMMENT LINE BELOW TO ACTUALLY DEFRAG
--EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
NOTES:
There are no notes on this topic