Cursor functionality via table variable and loop

-- cursor functionality WITHOUT using a cursor

declare @CurrentRow int
declare @TotalRows int
declare @FirstName nvarchar(255)

select @CurrentRow = 1

declare @TableVariable table
(
UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL ,
FirstName nvarchar(255)
)

insert into @TableVariable (FirstName) values ('Adam')
insert into @TableVariable (FirstName) values ('Bill')
insert into @TableVariable (FirstName) values ('Charlie')
insert into @TableVariable (FirstName) values ('Dennis')

select @TotalRows=count(*) from @TableVariable

while @CurrentRow <= @TotalRows  
begin   
select @FirstName = FirstName   
from @TableVariable   
where UniqueRowID = @CurrentRow    
print @FirstName    
select @CurrentRow = @CurrentRow + 1  
end 

Indexes : Included Columns

CREATE NONCLUSTERED INDEX IndexName 
ON Schema.TableName (ID
,int1
,int2
,text1
,text2
,text3
,text4
,text5)

Warning! The maximum key length is 900 bytes. The index 'IndexName' has maximum length of 938 bytes. For some combination of large values, the insert/update operation will fail.


The way round this in sql 2005+ - INCLUDED COLUMNS

Add the column as an included column >

CREATE NONCLUSTERED INDEX IndexName ON Schema.TableName 
(ID) INCLUDE (int1
,int2
,text1
,text2
,text3
,text4
,text5)

Counts of database objects

-- sql 2005 - database object counts

if object_id('tempdb..#objecttypes') is not null
begin
drop table #objecttypes
end
create table #objecttypes (otype char(5)collate Latin1_General_CI_AS ,typedesc varchar(50),primary key clustered(otype))

insert into #objecttypes(otype,typedesc) values('AF','Aggregate function (CLR)')
insert into #objecttypes(otype,typedesc) values('C','Check constraint')
insert into #objecttypes(otype,typedesc) values('D','Default (constraint or stand-alone)')
insert into #objecttypes(otype,typedesc) values('F','Foreign Key constraint')
insert into #objecttypes(otype,typedesc) values('PK','Primary Key constraint')
insert into #objecttypes(otype,typedesc) values('P','SQL Stored procedure')
insert into #objecttypes(otype,typedesc) values('PC','Assembly (CLR) stored procedure')
insert into #objecttypes(otype,typedesc) values('FN','SQL scalar function')
insert into #objecttypes(otype,typedesc) values('FS','Assembly (CLR) scalar function')
insert into #objecttypes(otype,typedesc) values('FT','Assembly (CLR) table-valued function')
insert into #objecttypes(otype,typedesc) values('R','Rule (old-style, stand-alone)')
insert into #objecttypes(otype,typedesc) values('RF','Replication-filter-procedure')
insert into #objecttypes(otype,typedesc) values('S','System base table')
insert into #objecttypes(otype,typedesc) values('SN','Synonym')
insert into #objecttypes(otype,typedesc) values('SQ','Service queue')
insert into #objecttypes(otype,typedesc) values('TA','Assembly (CLR) DML trigger')
insert into #objecttypes(otype,typedesc) values('TR','SQL DML trigger')
insert into #objecttypes(otype,typedesc) values('IF','SQL inline table-valued function')
insert into #objecttypes(otype,typedesc) values('TF','SQL table-valued-function')
insert into #objecttypes(otype,typedesc) values('U','Table (user-defined)')
insert into #objecttypes(otype,typedesc) values('UQ','Unique constraint')
insert into #objecttypes(otype,typedesc) values('V','View')
insert into #objecttypes(otype,typedesc) values('X','Extended stored procedure')
insert into #objecttypes(otype,typedesc) values('IT','Internal table')

select otype,typedesc,count(s.type) as total
from #objecttypes
left join sys.objects s
on #objecttypes.otype collate Latin1_General_CI_AS = s.type collate Latin1_General_CI_AS
group by otype, typedesc

TSQL : Checking for object existance...

IF EXISTS(SELECT Name FROM SysObjects WHERE Name='TEMP_MATCH_USERS' AND xType='U')
BEGIN
 PRINT 'Dropping table dbo.TEMP_MATCH_USERS...'
 DROP TABLE [dbo].[TEMP_MATCH_USERS]
END
GO

SQL 2000 : Table Indexes via System Objects

SELECT  SysUsers.[Name] AS Owner, Object_Name(SysIndexes.ID)  AS TableName, SysIndexes.[Name] AS IndexName, [InDID] 
From SysIndexes
Inner Join SysObjects On SysObjects.[ID]=SysIndexes.[ID]
Inner Join SysUsers On SysUsers.[UID]=SysObjects.[UID]
Where ObjectProperty(SysIndexes.[ID],'IsSystemTable')=0  
And ObjectProperty(SysIndexes.[ID],'IsUserTable')=1 
And [InDID]>0 
And [InDID]<255 
And [first] IS NOT NULL

Table Row Counts via system objects

-- Get Table Row Counts via system objects

SELECT sysobjects.name, sysindexes.rows
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'u'
AND sysindexes.indid < 2
order by sysindexes.rows desc

Truncate all tables

/*
Dynamic SQL to achieve this
*/
SELECT 'TRUNCATE TABLE [' +table_schema + '].[' + TABLE_NAME +']'
FROM INFORMATION_SCHEMA.TABLES 

TSQL Date Manipulation

Negate Time Part (Midnight for today)

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

Monday of the Current Week

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

First Day of Month

SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)

Last Day of Month

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,GETDATE())+1, 0))

First Day of the Year

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

Last Day of Year

SELECT DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,GETDATE())+1, 0))

Further uses for CASE Statements

Everyone knows you can use CASE statements in the SELECT clause to specify column output, but it can also be used in other ways >

Computed Columns -

BMI = CASE
WHEN (Height + Weight) IS NULL THEN 'Cannot Calculate'
ELSE Weight / Height
END

Conditional WHERE : Dealing with NULLs on the fly by using CASE statements.

For exact matches, check for columns being equal to themselves i.e. saying TRUE = TRUE

SELECT column1, column2, column3
FROM   schema.table
WHERE  datecolumn BETWEEN @dateRangeFrom AND @dateRangeTo
AND    column4 = CASE WHEN @criteria IS NULL THEN column4 ELSE @criteria END

To use the LIKE clause in the same way, ISNULL can be used -

AND textcolumn LIKE ISNULL(@searchterm,'%')

This replaces an empty search term with the wildcard '%' which of course matches any term.

Conditional ORDER BY -

SELECT column1, column2, column3
FROM   schema.table
ORDER BY
CASE WHEN @orderby = 'column1' THEN column1
WHEN @orderby = 'column2' THEN column2
WHEN @orderby = 'column3' THEN column3
END

SQL 101 : Storage

SQL Server Storage : Pages

A SQL database is made up of logical pages. A page is 8Kb (8192 bytes) in size and is used by both tables and indexes to store information. 8Kb is therefore the size of an input/output unit in SQL server.

*** Some special pages also exist for systems management purposes for example the ‘Global Allocation Map’, ‘Index Allocation Map’ and ‘Page Free Space’.

In versions up to SQL 2000, 8kb was the maximum row size in a table.

Prior to SQL 2005, defining a table with a larger row size is possible, but populating the columns fully was not.

For example, in SQL 7 - 2000 , the sql >

create table rubbishtable (

rubbishcolumn1 varchar (8000),
rubbishcolumn2 varchar (8000)

)

Returns the warning >

Warning: The table 'rubbishtable' has been created but its maximum row size (16023) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

SQL 2005

In SQL 2005, the page size is still 8Kb but you can exceed 8k per row.
This is achieved by moving the largest column to a second page into a ‘ROW_OVERFLOW_DATA unit’. The original page is then updated by adding a 24 byte pointer to this second page. This is known as the ‘IN_ROW_DATA allocation unit’.

You don’t see this functionality happening. ‘Oh great’ , ‘I can now fit more data into a row’ is likely to be a developer’s response to this new feature.

From a DBA’s point of view however, utilising this new functionality has performance implications.
Performing queries on tables where the data is split across pages will be marginally slower due to 2 pages being returned for each offending record.

Also, when updates are done which force the creation or deletion of overflow pages this will cause fragmentation in the database.



SQL Server Storage : Extents

An Extent is a group of 8 pages and hence is (8x8kb) 64Kb in size. This is the smallest unit of data SQL Server can allocate.

There are two types of extent.

Uniform Extent All 8 pages are assigned to a single object.

Mixed Extent More than 1 (small) object is placed inside the extent, ensuring space is not wasted.




SQL Server Storage : Files

Each datafile in a SQL database has 2 names, a logical file name (used to refer to the data store from transact SQL) and a physical one. The Physical one is the one stored on the operating system.


SQL databases have 3 filetypes.

Every database has a primary data store. The recommended (but not enforced) file extension is .MDF.

Some databases have (1 or more) secondary data files. The recommended (but not enforced) file extension is .NDF.

Log Files should have a .LDF extension. The log file (also known as the transaction log) holds a log of changes which may be helpful in recovering the datanase. When the recovery model is set to ‘simple’ they contain minimal information however.


SQL Server Storage : File Groups

File groups come in 2 flavours, ‘Primary’ or ‘User Defined’. Either of these can be the default file group i.e where all user objects are created (unless filegroup is specified).

File groups allow you to place heavily accessed tables in a different filegroup. Reasons for this could be to improve database performance (e.g housing the filegroup on a different disk array) or to backup that data separately.