How To Create IsWeekday Function In T-SQL To Determine If A Date Is A Weekday Or Weekend



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

How To Update From Using T-SQL



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.

How To Use a CASE Statement In T-SQL



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.

How To Incrementally Shrink A SQL Server Database Using T-SQL



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

How To Create a Comma Separated List Of Table Columns Using T-SQL



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.

How To Create An Index Using T-SQL



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.

How To Add A Default Constraint To An Existing Column Using T-SQL



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

How To Use A CASE Statement In A Select In T-SQL (SQL Server)



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.

How To Drop A Column From A Table In SQL Server Using T-SQL



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

How to Find And Rebuild Fragmented Indexes In SQL Server Using T-SQL



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