What are the difference between DDL, DML and DCL commands?

Submitted by admin on Wed, 2004-08-04 13:49

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

GRANT - gives user’s access privileges to database

REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

What are the difference between DDL, DML and DCL commands? | Oracle FAQ.

Is the Relational Database Doomed?

.

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

– Oct  2 2008 11:01AM

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

– Oct  2 2008 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

– 02 Oct 2008 11:02:07:577

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

– 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy

————

– SQL Server date formatting function – convert datetime to string

————

– SQL datetime functions

– SQL Server date formats

– T-SQL convert dates

– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

DECLARE @StringDate VARCHAR(32)

SET @StringDate = @FormatMask

IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

DATENAME(YY, @Datetime))

IF (CHARINDEX (‘YY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YY’,

RIGHT(DATENAME(YY, @Datetime),2))

IF (CHARINDEX (‘Month’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Month’,

DATENAME(MM, @Datetime))

IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

SET @StringDate = REPLACE(@StringDate, ‘MON’,

LEFT(UPPER(DATENAME(MM, @Datetime)),3))

IF (CHARINDEX (‘Mon’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Mon’,

LEFT(DATENAME(MM, @Datetime),3))

IF (CHARINDEX (‘MM’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘MM’,

RIGHT(‘0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

IF (CHARINDEX (‘M’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘M’,

CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

IF (CHARINDEX (‘DD’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘DD’,

RIGHT(‘0′+DATENAME(DD, @Datetime),2))

IF (CHARINDEX (‘D’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘D’,

DATENAME(DD, @Datetime))

RETURN @StringDate

END

GO

– Microsoft SQL Server date format function test

– MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) – 2012-01-03

– CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’) – 12.01.03

GO

————

/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

– SQL format datetime

– Default format: Oct 23 2006 10:40AM

SELECT [Default]=CONVERT(varchar,GETDATE(),100)

– US-Style format: 10/23/2006

SELECT [US-Style]=CONVERT(char,GETDATE(),101)

– ANSI format: 2006.10.23

SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

– UK-Style format: 23/10/2006

SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

– German format: 23.10.2006

SELECT [German]=CONVERT(varchar,GETDATE(),104)

– ISO format: 20061023

SELECT ISO=CONVERT(varchar,GETDATE(),112)

– ISO8601 format: 2008-10-23T19:20:16.003

SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

————

– SQL Server datetime formats

– Century date format MM/DD/YYYY usage in a query

– Format dates SQL Server 2005

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(char(10), OrderDate, 101),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

/* Result

SalesOrderID      OrderDate               OrderDateTime

43697             07/01/2001          2001-07-01 00:00:00.000

*/

– SQL update datetime column

– SQL datetime DATEADD

UPDATE Production.Product

SET ModifiedDate=DATEADD(dd,1, ModifiedDate)

WHERE ProductID = 1001

– MM/DD/YY date format

– Datetime format sql

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(varchar(8), OrderDate, 1),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY SalesOrderID desc

/* Result

SalesOrderID      OrderDate         OrderDateTime

75123             07/31/04          2004-07-31 00:00:00.000

*/

– Combining different style formats for date & time

– Datetime formats

– Datetime formats sql

DECLARE @Date DATETIME

SET @Date = ‘2015-12-22 03:51 PM’

SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)

– Result: 12-22-2015  3:51PM

– Microsoft SQL Server cast datetime to string

SELECT stringDateTime=CAST (getdate() as varchar)

– Result: Dec 29 2012  3:47AM

————

– SQL Server date and time functions overview

————

– SQL Server CURRENT_TIMESTAMP function

– SQL Server datetime functions

– local NYC – EST – Eastern Standard Time zone

– SQL DATEADD function – SQL DATEDIFF function

SELECT CURRENT_TIMESTAMP – 2012-01-05 07:02:10.577

– SQL Server DATEADD function

SELECT DATEADD(month,2,‘2012-12-09′) – 2013-02-09 00:00:00.000

– SQL Server DATEDIFF function

SELECT DATEDIFF(day,‘2012-12-09′,‘2013-02-09′) – 62

– SQL Server DATENAME function

SELECT DATENAME(month, ‘2012-12-09′) – December

SELECT DATENAME(weekday, ‘2012-12-09′) – Sunday

– SQL Server DATEPART function

SELECT DATEPART(month, ‘2012-12-09′) – 12

– SQL Server DAY function

SELECT DAY(‘2012-12-09′) – 9

– SQL Server GETDATE function

– local NYC – EST – Eastern Standard Time zone

SELECT GETDATE() – 2012-01-05 07:02:10.577

– SQL Server GETUTCDATE function

– London – Greenwich Mean Time

SELECT GETUTCDATE() – 2012-01-05 12:02:10.577

– SQL Server MONTH function

SELECT MONTH(‘2012-12-09′) – 12

– SQL Server YEAR function

SELECT YEAR(‘2012-12-09′) – 2012

————

– T-SQL Date and time function application

– CURRENT_TIMESTAMP and getdate() are the same in T-SQL

————

– SQL first day of the month

– SQL first date of the month

– SQL first day of current month – 2012-01-01 00:00:00.000

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of the month

– SQL last date of the month

– SQL last day of current month – 2012-01-31 00:00:00.000

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

– SQL first day of last month

– SQL first day of previous month – 2011-12-01 00:00:00.000

SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of last month

– SQL last day of previous month – 2011-12-31 00:00:00.000

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

– SQL first day of next month – 2012-02-01 00:00:00.000

SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of next month – 2012-02-28 00:00:00.000

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

GO

– SQL first day of a month – 2012-10-01 00:00:00.000

DECLARE @Date datetime; SET @Date = ‘2012-10-23′

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))

GO

– SQL last day of a month – 2012-03-31 00:00:00.000

DECLARE @Date datetime; SET @Date = ‘2012-03-15′

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

GO

– SQL first day of year

– SQL first day of the year  -  2012-01-01 00:00:00.000

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

– SQL last day of year

– SQL last day of the year   – 2012-12-31 00:00:00.000

SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,

DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))

– SQL last day of last year

– SQL last day of previous year   – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))

GO

– SQL calculate age in years, months, days

– SQL table-valued function

– SQL user-defined function – UDF

– SQL Server age calculation – date difference

– Format dates SQL Server 2008

USE AdventureWorks2008;

GO

CREATE FUNCTION fnAge (@BirthDate DATETIME)

RETURNS @Age TABLE(Years INT,

Months INT,

Days INT)

AS

BEGIN

DECLARE @EndDate DATETIME, @Anniversary DATETIME

SET @EndDate = Getdate()

SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)

INSERT @Age

SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE

WHEN @Anniversary > @EndDate THEN 1

ELSE 0

END), 0, 0

UPDATE @Age SET Months = Month(@EndDate - @Anniversary) - 1

UPDATE @Age SET Days = Day(@EndDate - @Anniversary) - 1

RETURN

END

GO

– Test table-valued UDF

SELECT * FROM fnAge(‘1956-10-23′)

SELECT * FROM dbo.fnAge(‘1956-10-23′)

/* Results

Years       Months      Days

52          4           1

*/

———-

– SQL date range between

———-

– SQL between dates

USE AdventureWorks;

– SQL between

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN ‘20040301′ AND ‘20040315′

– Result: 108

– BETWEEN operator is equivalent to >=…AND….<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN ‘2004-03-01 00:00:00.000′ AND ‘2004-03-15  00:00:00.000′

/*

Orders with OrderDates

‘2004-03-15  00:00:01.000′  – 1 second after midnight (12:00AM)

‘2004-03-15  00:01:00.000′  – 1 minute after midnight

‘2004-03-15  01:00:00.000′  – 1 hour after midnight

are not included in the two queries above.

*/

– To include the entire day of 2004-03-15 use the following two solutions

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= ‘20040301′ AND OrderDate < ‘20040316′

– SQL between with DATE type (SQL Server 2008)

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE CONVERT(DATE, OrderDate) BETWEEN ‘20040301′ AND ‘20040315′

———-

– Non-standard format conversion: 2011 December 14

– SQL datetime to string

SELECT [YYYY Month DD] =

CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+

DATENAME(MM, GETDATE()) + ‘ ‘ +

CAST(DAY(GETDATE()) AS VARCHAR(2))

– Converting datetime to YYYYMMDDHHMMSS format: 20121214172638

SELECT replace(convert(varchar, getdate(),111),‘/’,”) +

replace(convert(varchar, getdate(),108),‘:’,”)

– Datetime custom format conversion to YYYY_MM_DD

select CurrentDate=rtrim(year(getdate())) + ‘_’ +

right(‘0′ + rtrim(month(getdate())),2) + ‘_’ +

right(‘0′ + rtrim(day(getdate())),2)

– Converting seconds to HH:MM:SS format

declare @Seconds int

set @Seconds = 10000

select TimeSpan=right(‘0′ +rtrim(@Seconds / 3600),2) + ‘:’ +

right(‘0′ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +

right(‘0′ + rtrim(@Seconds % 60),2)

– Result: 02:46:40

– Test result

select 2*3600 + 46*60 + 40

– Result: 10000

– Set the time portion of a datetime value to 00:00:00.000

– SQL strip time from date

– SQL strip time from datetime

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

– Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000

/*******

VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

SMALLDATETIME date range:

January 1, 1900 through June 6, 2079

DATETIME date range:

January 1, 1753 through December 31, 9999

DATETIME2 date range (SQL Server 2008):

January 1,1 AD through December 31, 9999 AD

DATE date range (SQL Server 2008):

January 1, 1 AD through December 31, 9999 AD

*******/

– Selecting with CONVERT into different styles

– Note: Only Japan & ISO styles can be used in ORDER BY

SELECT TOP(1)

Italy = CONVERT(varchar, OrderDate, 105)

, USA = CONVERT(varchar, OrderDate, 110)

, Japan = CONVERT(varchar, OrderDate, 111)

, ISO = CONVERT(varchar, OrderDate, 112)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY PurchaseOrderID DESC

/* Results

Italy       USA         Japan       ISO

25-07-2004  07-25-2004  2004/07/25  20040725

*/

– SQL Server convert date to integer

DECLARE @Datetime datetime

SET @Datetime = ‘2012-10-23 10:21:05.345′

SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)

– Result: 20121023

– SQL Server convert integer to datetime

DECLARE @intDate int

SET @intDate = 20120315

SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)

– Result: 2012-03-15 00:00:00.000

————

– SQL Server CONVERT script applying table INSERT/UPDATE

————

– SQL Server convert date

– Datetime column is converted into date only string column

USE tempdb;

GO

CREATE TABLE sqlConvertDateTime (

DatetimeCol datetime,

DateCol char(8));

INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

UPDATE sqlConvertDateTime

SET DateCol = CONVERT(char(10), DatetimeCol, 112)

SELECT * FROM sqlConvertDateTime

– SQL Server convert datetime

– The string date column is converted into datetime column

UPDATE sqlConvertDateTime

SET DatetimeCol = CONVERT(Datetime, DateCol, 112)

SELECT * FROM sqlConvertDateTime

– Adding a day to the converted datetime column with DATEADD

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))

SELECT * FROM sqlConvertDateTime

– Equivalent formulation

– SQL Server cast datetime

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))

SELECT * FROM sqlConvertDateTime

GO

DROP TABLE sqlConvertDateTime

GO

/* First results

DatetimeCol                   DateCol

2014-12-25 16:04:15.373       20141225 */

/* Second results:

DatetimeCol                   DateCol

2014-12-25 00:00:00.000       20141225  */

/* Third results:

DatetimeCol                   DateCol

2014-12-26 00:00:00.000       20141225  */

————

– SQL month sequence – SQL date sequence generation with table variable

– SQL Server cast string to datetime – SQL Server cast datetime to string

– SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

DECLARE @StartDate datetime;

SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+

RIGHT(‘0′+convert(varchar,month(getdate())),2) + ‘01′ AS DATETIME)

WHILE ( @i < 120)

BEGIN

INSERT @Sequence DEFAULT VALUES

SET @i = @i + 1

END

SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)

FROM @Sequence

GO

/* Partial results:

MonthSequence

Jan  1 2012 12:00AM

Feb  1 2012 12:00AM

Mar  1 2012 12:00AM

Apr  1 2012 12:00AM

*/

————

————

– SQL Server Server datetime internal storage

– SQL Server datetime formats

————

– SQL Server datetime to hex

SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))

/* Results

Now                     HexNow

2009-01-02 17:35:59.297 0×00009B850122092D

*/

– SQL Server date part – left 4 bytes – Days since 1900-01-01

SELECT Now=DATEADD(DAY, CONVERT(INT, 0×00009B85), ‘19000101′)

GO

– Result: 2009-01-02 00:00:00.000

– SQL time part – right 4 bytes – milliseconds since midnight

– 1000/300 is an adjustment factor

– SQL dateadd to Midnight

SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0×0122092D), ‘2009-01-02′)

GO

– Result: 2009-01-02 17:35:59.290

————

————

– String date and datetime date&time columns usage

– SQL Server datetime formats in tables

————

USE tempdb;

SET NOCOUNT ON;

– SQL Server select into table create

SELECT TOP (5)

FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),

BirthDate = CONVERT(char(8), BirthDate,112),

ModifiedDate = getdate()

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.Person.Contact c

ON c.ContactID = e.ContactID

ORDER BY EmployeeID

GO

– SQL Server alter table

ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL

GO

ALTER TABLE Employee

ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )

GO

/* Results

Table definition for the Employee table

Note: BirthDate is string date (only)

CREATE TABLE dbo.Employee(

FullName nvarchar(50) NOT NULL PRIMARY KEY,

BirthDate char(8) NULL,

ModifiedDate datetime NOT NULL

)

*/

SELECT * FROM Employee ORDER BY FullName

GO

/* Results

FullName                BirthDate   ModifiedDate

Guy Gilbert             19720515    2009-01-03 10:10:19.217

Kevin Brown             19770603    2009-01-03 10:10:19.217

Rob Walters             19650123    2009-01-03 10:10:19.217

Roberto Tamburello      19641213    2009-01-03 10:10:19.217

Thierry D’Hers          19490829    2009-01-03 10:10:19.217

*/

– SQL Server age

SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),

RowMaintenanceDate = CAST (ModifiedDate AS varchar)

FROM Employee ORDER BY FullName

GO

/* Results

FullName                Age   RowMaintenanceDate

Guy Gilbert             37    Jan  3 2009 10:10AM

Kevin Brown             32    Jan  3 2009 10:10AM

Rob Walters             44    Jan  3 2009 10:10AM

Roberto Tamburello      45    Jan  3 2009 10:10AM

Thierry D’Hers          60    Jan  3 2009 10:10AM

*/

– SQL Server age of Rob Walters on specific dates

– SQL Server string to datetime implicit conversion with DATEADD

SELECT AGE50DATE = DATEADD(YY, 50, ‘19650123′)

GO

– Result: 2015-01-23 00:00:00.000

– SQL Server datetime to string, Italian format for ModifiedDate

– SQL Server string to datetime implicit conversion with DATEDIFF

SELECT FullName,

AgeDEC31 = DATEDIFF(YEAR, BirthDate, ‘20141231′),

AgeJAN01 = DATEDIFF(YEAR, BirthDate, ‘20150101′),

AgeJAN23 = DATEDIFF(YEAR, BirthDate, ‘20150123′),

AgeJAN24 = DATEDIFF(YEAR, BirthDate, ‘20150124′),

ModDate = CONVERT(varchar, ModifiedDate, 105)

FROM Employee

WHERE FullName = ‘Rob Walters’

ORDER BY FullName

GO

/* Results

Important Note: age increments on Jan 1 (not as commonly calculated)

FullName    AgeDEC31    AgeJAN01    AgeJAN23    AgeJAN24    ModDate

Rob Walters 49          50          50          50          03-01-2009

*/

————

– SQL combine integer date & time into datetime

————

– Datetime format sql

– SQL stuff

DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,

DateAsINT int,

TimeAsINT int

)

– NOTE: leading zeroes in time is for readability only!

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)

SELECT DateAsINT, TimeAsINT,

CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+

STUFF(STUFF ( RIGHT(REPLICATE(‘0′, 6) + CONVERT(varchar(6), TimeAsINT), 6),

3, 0, ‘:’), 6, 0, ‘:’)) AS DateTimeValue

FROM @DateTimeAsINT

ORDER BY ID

GO

/* Results

DateAsINT   TimeAsINT   DateTimeValue

20121023    235959      2012-10-23 23:59:59.000

20121023    10204       2012-10-23 01:02:04.000

20121023    2350        2012-10-23 00:23:50.000

20121023    244         2012-10-23 00:02:44.000

20121023    50          2012-10-23 00:00:50.000

20121023    6           2012-10-23 00:00:06.000

*/

————

– SQL Server string to datetime, implicit conversion with assignment

UPDATE Employee SET ModifiedDate = ‘20150123′

WHERE FullName = ‘Rob Walters’

GO

SELECT ModifiedDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 2015-01-23 00:00:00.000

/* SQL string date, assemble string date from datetime parts  */

– SQL Server cast string to datetime – sql convert string date

– SQL Server number to varchar conversion

– SQL Server leading zeroes for month and day

– SQL Server right string function

UPDATE Employee SET BirthDate =

CONVERT(char(4),YEAR(CAST(‘1965-01-23′ as DATETIME)))+

RIGHT(‘0′+CONVERT(varchar,MONTH(CAST(‘1965-01-23′ as DATETIME))),2)+

RIGHT(‘0′+CONVERT(varchar,DAY(CAST(‘1965-01-23′ as DATETIME))),2)

WHERE FullName = ‘Rob Walters’

GO

SELECT BirthDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 19650123

– Perform cleanup action

DROP TABLE Employee

– SQL nocount

SET NOCOUNT OFF;

GO

————

————

– sql isdate function

————

USE tempdb;

– sql newid – random sort

SELECT top(3) SalesOrderID,

stringOrderDate = CAST (OrderDate AS varchar)

INTO DateValidation

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY NEWID()

GO

SELECT * FROM DateValidation

/* Results

SalesOrderID      stringOrderDate

56720             Oct 26 2003 12:00AM

73737             Jun 25 2004 12:00AM

70573             May 14 2004 12:00AM

*/

– SQL update with top

UPDATE TOP(1) DateValidation

SET stringOrderDate = ‘Apb 29 2004 12:00AM’

GO

– SQL string to datetime fails without validation

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

GO

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an

out-of-range value.

*/

– sql isdate – filter for valid dates

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

WHERE ISDATE(stringOrderDate) = 1

GO

/* Results

SalesOrderID      OrderDate

73737             2004-06-25 00:00:00.000

70573             2004-05-14 00:00:00.000

*/

– SQL drop table

DROP TABLE DateValidation

Go

————

– SELECT between two specified dates – assumption TIME part is 00:00:00.000

————

– SQL datetime between

– SQL select between two dates

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ‘1997-11-01′ AND

RateChangeDate < DATEADD(dd,1,‘1998-01-05′)

GO

/* Results

EmployeeID  RateChangeDate

3           1997-12-12 00:00:00.000

4           1998-01-05 00:00:00.000

*/

/* Equivalent to

– SQL datetime range

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ‘1997-11-01 00:00:00′ AND

RateChangeDate <  ‘1998-01-06 00:00:00′

GO

*/

————

– SQL datetime language setting

– SQL Nondeterministic function usage – result varies with language settings

SET LANGUAGE ‘us_english’; –– Jan 12 2015 12:00AM

SELECT US = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘British’; –– Dec 1 2015 12:00AM

SELECT UK = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘German’; –– Dez  1 2015 12:00AM

SET LANGUAGE ‘Deutsch’; –– Dez  1 2015 12:00AM

SELECT Germany = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘French’; –– déc  1 2015 12:00AM

SELECT France = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘Spanish’; –– Dic  1 2015 12:00AM

SELECT Spain = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘Hungarian’; –– jan 12 2015 12:00AM

SELECT Hungary = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘us_english’;

GO

————

————

– Function for Monday dates calculation

————

USE AdventureWorks2008;

GO

– SQL user-defined function

– SQL scalar function – UDF

CREATE FUNCTION fnMondayDate

(@Year INT,

@Month INT,

@MondayOrdinal INT)

RETURNS DATETIME

AS

BEGIN

DECLARE @FirstDayOfMonth CHAR(10),

@SeedDate CHAR(10)

SET @FirstDayOfMonth = convert(VARCHAR,@Year) + ‘-’ + convert(VARCHAR,@Month) + ‘-01′

SET @SeedDate = ‘1900-01-01′

RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,

@FirstDayOfMonth)) / 7 * 7, @SeedDate)

END

GO

– Test Datetime UDF

– Third Monday in Feb, 2015

SELECT dbo.fnMondayDate(2016,2,3)

– 2015-02-16 00:00:00.000

– First Monday of current month

SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)

– 2009-02-02 00:00:00.000

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I’ll discuss the differences between using temporary tables in SQL Server versus table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:

• Normal tables are exactly that, physical tables defined in your database.
• Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
• Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
• Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It’s a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Creating indexes on SQL Server tables

Because both local and global temporary tables are physical tables within the tempdb database, indexes can be created on these tables to increase performance as needed. As with any index creation, this process can take time on larger tables. Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.

Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.

How do the internal workings of SQL Server perform differently between table variables and temporary tables?

The differences between accessing tables and variables cause the internal processes within SQL Server to treat the objects quite differently. Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tables just as they would any other database tables. Because reads to a temporary table are made (including local temporary tables), a read lock is placed on the table.

This locking process takes time and CPU resources. When reading from a table variable – because the table variable is stored partially within memory and cannot be accessed by any other user or process on the system – SQL Server knows locking is not required. In a very busy database, this lack of locking can improve system performance because locks do not have to be taken, escalated and checked for each data access operation.

Limits of temporary tables and table variables

Temporary tables and table variables both have their strengths, but they both have weaknesses too. On a heavy load system that has lots of usage of temporary tables, the disk array servicing the tempdb database will experience a higher than expected load. This happens because all reads and writes to the temporary tables are done within the tempdb database. Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables — large amounts of RAM are used because all temporary tables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).

Both temporary tables and table variables can be extremely useful tools in developers’ and administrators’ arsenals; however, care must be taken as to when to use each solution. There is no end-all solution, and you must choose the correct solution for the correct situation.

Local Temporary tables:

They are created using same syntax as CREATE TABLE except table name is preceded by ‘#’ sign. When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.

Open one session in Query Analyzer or SSMS (Management Studio) and create a temporary table as shown below.

CREATE TABLE #TEMP
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)

GO

Upon successful execution of above command, MS SQL Server creates table in tempdb database. One cannot create another temporary table with the same name in the same session. It will give an error but table with the same name can be created from another session. To do this, open another session from SSMS or query analyzer and issue same command again. It will successfully create new temporary table for that session.

In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number. This is very common scenario when temporary table is defined in the stored procedure and procedure is getting executed by different users simultaneously. Since we have created temporary table with the same name from two different sessions, we should see two entries in tempdb database. From another session or any of the current session, issue following command. Output is displayed after select statement.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘%TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb #TEMP________0000000001F7
tempdb #TEMP________0000000001F9

Now create some data from the session in which temporary table (#temp) is created.

INSERT INTO #TEMP(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO #TEMP(COL1, COL2) VALUES(2,’Information’);
INSERT INTO #TEMP(COL1, COL2) VALUES(3,’systems’);

Selecting data from temporary table will give following results.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-27 19:39:56.727
2 Information 2007-03-27 19:39:56.727
3 systems 2007-03-27 19:39:56.727

This data is not visible from another session since we are using local temporary table. We can verify it by connecting to another session and querying the #temp table. Local temporary tables are dropped when session which created the table is ended, if one has not dropped it explicitly.

Also, please do note that if you are creating temp tables in a stored procedure, the scope for the existence of those temporary tables is only the procedure execution. The temp tables automatically get dropped once the procedure execution is over (they can be explicitly dropped as well). Once the procedure execution is over, those temp tables will not be accessible from within that session. Example:

create proc test
as
begin
set nocount on
create table #temp (col1 int)
insert into #temp values (1)
end
go

exec test
select * from #temp

Msg 208, Level 16, State 0, Line 2
Invalid object name ‘#temp’.

Global Temporary tables:

Syntax difference between global and local temporary table is of an extra ‘#’ sign. Global temporary tables are preceded with two ‘#’ (##) sign. Following is the definition. In contrast of local temporary tables, global temporary tables are visible across entire instance.

CREATE TABLE ##TEMP_GLOBAL
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)
GO

Execute above statement to create global temporary table. You can verify it by checking the tempdb database. As global temporary tables are available across the instance, SQL Server doesn’t suffix it with the number. Following is the output of query ran against tempdb.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘##TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb ##TEMP_GLOBAL

There will be only single instance of global temporary table. Attempt of creating global temporary table with the same name from any other session will result into an error.

Create some data in one of the session where temporary table (##temp_global) is created.

INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(2,’Information’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(3,’systems’);

Connect to other existing session or open new session. Execute following statement and you will notice that global temporary table is available along with the data from other session as well.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-28 09:52:34.310
2 Information 2007-03-28 09:52:34.310
3 systems 2007-03-28 09:52:34.310

Global temporary tables are dropped when last session accessing the tables is closed. It is always good practice to drop the temporary tables in the same scope, once we are done with it. This will help us in avoiding creation error when same connection from the connection pool is used by different processes which access temporary tables.

Global temporary tables can be used in data warehousing application where one session performs the ETL and populate the global temporary tables and other sessions read from the table, specific data and process it.

Features of Temp Tables

We’ll list out features that differentiate a Temp Table between either a Permanent Table or a Table variable. These pointers will be helpful to keep in mind when you consider Table Variables in our next post.

Scope: Within a connection, a temporary table object is visible to the creating level and inner levels (nested). For example, if you create a stored procedure and declare a temporary table object within it, you can call another stored procedure from that stored procedure (a nested stored procedure) and perform operations like inserting, updating and deleting that temporary table object. Once the main creating level terminates, the temp table is automatically destroyed. But don’t be too complacent – you’ll have to wait for the system to perform a clean up and therefore, it is highly recommended that you manually drop your temporary table.

Locking: The prospect of table locking is reduced when it comes to local temporary tables since this table is being used by only one user. One aspect where you might want to keep this in mind is that if you cancel a transaction which contains the creation of a temp table object and then cancel that query, an exclusive and update lock can appear on the tempdb. This lock will persist till the complete transaction has closed with a COMMIT or a ROLLBACK

Logging: There is less logging involved with temporary tables compared to permanent tables.

Transaction: When using a temporary table, a temporary table is an integral part of an outer transaction and therefore, ROLLBACKs must be supported by Logging

Indexing: We can create indexes on temporary tables explicitly on them. Hence, there is scope for performance enhancement when you talk about temp tables.

Constraints: All constraints are available for exploiting on a temp table EXCEPT when it comes to referring a Foreign Key Constraints

Statistics: SQL Server can create Statistics for temp tables just like we do for permanent tables and therefore, the query optimizer has the option of choosing different plans. Hence, with this in mind, be aware of the scope of Stored Procedure Recompiles.

Recompiles: There is scope for a large number of Stored Procedure Recompiles especially when you have DDL statements mixed anywhere within your Stored Procedure.

Temp Table Size: Can hold any volume of data. This will be a strong part of the deciding factor when you want to choose between a temporary table and a table variable.

Features of Table Variables

Now that you’ve got a hang around working with a Table Variable, let me mention the main pointers that we need to keep in mind while working with. This will set the stage for differentiating between a Temp Table and Table Variable which I’ll illustrate in my next post.

Transactions: Table Variables are not bound to any transaction as they are just like any other variable

Minimum Constraints: A Table Variable permits us to use only the PRIMARY KEY, UNIQUE KEY and NULL constraint only. What this implies behind the scenes is that we can have unique indexes. The only possibility of creating a non-unique index is if we add attributes and make that blend unique and have a PRIMARY KEY or a UNIQUE KEY on the combination we just made.

No SELECT INTO: We cannot use a SELECT INTO with Table Variables in SQL Server 2000 though the feature is available with Table Variables in SQL Server 2005. Likewise, we can also have INSERT INTO working with Table Variables against a SELECT but not against an EXEC Stored Procedure.

No ALTER TABLE Variable: We cannot ALTER a Table once it has been declared. This may look a little rigid but remember that recompilations can come out like wild fire when there are DDL (Data Definition Language .i.e Schema) changes and therefore, this helps to avoid recompilations.

Scope: Just like any other variable, a Table Variable’s scope exists only within the context of the current level. Therefore, unlike Temp Tables, it is not accessible to sub levels (of Stored Procedures)

Table Variables And The TempDb: Okay, now I’ll touch upon one of the most common myths that exist among developers: that Table Variables have nothing to do with TempDb and therefore, they have no physical representation in the TempDb and therefore, they reside in ONLY memory and therefore they’re the best option for efficient processing.

Not entirely true. Table Variables do indeed have a physical representation within the TempDb and this can proved with a simple query in your database against the TempDb:

No Statistics: When it comes to Table Variables, the SQL Server optimizer does not create distribution statistics. Therefore, you run the risk of referring not-so-good query plans when the SQL Server optimizer selects after checking up with histograms. And if you consider this aspect with Tables Variables that contain huge amounts of data, we fall into serious I/O thrashing. Hence, as stated in the closing section of the last point, we have to have a thorough understanding of our scenario to choose a temporary object for the context.

A possible replacement for temp tables is a table variable.

In summary, following are the key points when temporary tables are involved.

• Temporary tables can be defined as local or global temporary tables.
• Local temporary tables are available to session in which they are created. If another  session creates the table with the same name, it will be different copy of the table in tempdb database.
• Global temporary tables are available across the instance. Any user from any session can access it.
• It is best practice to drop the temporary table when related work is finished rather than relying on connection to end for the cleanup.
• Table variables can be used instead of temporary tables for performance reasons and when dealing with smaller sub-sets.
• When used in the procedure,function or trigger, its scope ends once execution is completed.

Limitations of Temporary Tables

Temporary tables are created in the tempdb database and create additional overhead for SQL Server, reducing overall performances. SQL Server has numerous problems with operations against temporary tables.

Using Temporary Tables Effectively

If you do not have any option other than to use temporary tables, use them affectively. There are few steps to be taken.

• Only include the necessary columns and rows rather than using all the columns and all the data which will not make sense of using temporary tables. Always filter your data into the temporary tables.
• When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements, create the table using DDL statement and use INSERT INTO to populate the temporary table.
• Use indexes on temporary tables. Earlier days, I always forget to use a index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables.
• After you finish the using your temporary table, delete them. This will free the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time.
• When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query.

Conclusion

Generally, temporary tables should be avoided as much as possible. If you need to use them follow the steps above so that you have the minimum impact on server performance

If you have to use a temp table, do not create it from within a transaction. If you do, then it will lock some system tables (syscolumns, sysindexes, and syscomments) and prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance.

Source: http://dev.digi-corp.com/2009/05/temporary-tables-vs-table-variables-in-sql-server/

Here I collect some useful article links. I used SRSS 2005 for a while. This seems a very powerful tool. It maybe useful to distribute our reports more efficiently and neat.

Blog Site:

Prologika: http://prologika.com/CS/blogs/blog/default.aspx

Bob’s SQL Reporting Services blog: http://blogs.msdn.com/bobmeyers/default.aspx

SQL Server reporting services team blog: http://blogs.msdn.com/sqlrsteamblog/

Chris Hay’s Blog: http://blogs.msdn.com/chrishays/default.aspx

Robert Bruckner’s Blog: http://blogs.msdn.com/robertbruckner/default.aspx

Tips:

RSS Rules: http://www.ssw.com.au/Ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx

SQL Server Reporting Services - Optional Parameters: http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx

Report Design Tips and Tricks: http://msdn.microsoft.com/en-us/library/bb395166.aspx

Writing Custom Code in SQL Server Reporting Services: http://blogs.sqlxml.org/bryantlikes/pages/824.aspx

Integrating .NET Code and SQL Server Reporting Services: http://www.code-magazine.com/articleprint.aspx?quickid=0701061&printmode=true

Integrating .NET Code and SQL Server Reporting Services: http://www.devx.com/codemag/Article/33656/1954

Get More Out of SQL Server Reporting Services Charts: http://msdn.microsoft.com/en-us/library/aa964128(SQL.90).aspx

Report Builder: Creating a Report Model: http://www.databasejournal.com/features/mssql/article.php/3598931

Advanced Matrix Reporting Techniques: http://www.devx.com/dbzone/Article/37703/0/page/1

Advanced Ranking and Grouping with SQL Server Reporting Services: http://www.devx.com/dbzone/Article/37799

MSSQL Server Reporting Services Series of Tutorials:

Tutorial 1: A New Paradigm for Enterprise Reporting
Tutorial 2: The Authoring Phase: Overview Part I
Tutorial 3: The Authoring Phase: Overview Part II
Tutorial 4: Managing Reporting Services: Data Connections and Uploads
Tutorial 5: Managing Reporting Services: Report Execution and Standard Subscriptions
Tutorial 6: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
Tutorial 7: Mastering OLAP Reporting: Cascading Prompts
Tutorial 8: Master Chart Reports: Pie Charts in Reporting Services
Tutorial 9: Master Chart Reports: Track Exchange Rates in a Line Chart
Tutorial 10: Reporting Services Basics: Create a Reusable Template Report
Tutorial 11: Black Belt Components: Manage Nulls in OLAP Reports
Tutorial 12: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
Tutorial 13: Black Belt Administration: Prepare the Execution Log for Reporting
Tutorial 14: Black Belt Administration: Execution Log Performance and Audit Reports
Tutorial 15: Black Belt Administration: “Governor” Capabilities: Report Execution Timeout
Tutorial 16: Black Belt Components: Ad Hoc Sorting with Parameters
Tutorial 17: Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
Tutorial 18: Mastering OLAP Reporting: Percent of Total - Two Perspectives
Tutorial 19: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
Tutorial 20: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
Tutorial 21: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
Tutorial 22: Mastering OLAP Reporting: Drilling Through Using MDX
Tutorial 23: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
Tutorial 24: Interactive Sorting Within Reporting Services
Tutorial 25: Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Tutorial 26: Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Tutorial 27: Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Tutorial 28: Report Builder: Creating a Report Model
Tutorial 29: Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Tutorial 30: BlackBelt Administration: Linked Reports in Report Manager
Tutorial 31: BlackBelt Administration: Linked Reports in SQL Server Management Studio
Tutorial 32: Mastering OLAP Reporting: Prototype KPIs in Reporting Services
Tutorial 33: BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Tutorial 34: Black Belt Components: Interactive Sorts within a Matrix Data Region
Tutorial 35: Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Tutorial 36: Black Belt Components: Support Simple Navigation with a Document Map
Tutorial 37: Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Tutorial 38: Mastering OLAP Reports: Extend Reporting Services with Custom Code
Tutorial 39: Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Tutorial 40: Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Tutorial 41: Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Tutorial 42: Black Belt Administration: Reporting Services Configuration Manager
Tutorial 43: Black Belt Administration: Caching Options: Report Session Caching
Tutorial 44: Black Belt Administration: Report Execution Caching I: SQL Server Management Studio Perspective
Tutorial 45: Report Execution Caching II: Report Manager Perspective
Tutorial 46: Snapshot Reports I: Report Manager Perspective
Tutorial 47: Snapshot Reports II: SQL Server Management Studio Perspective
Tutorial 48: Reporting Services: Customize Automatically Created Parameter Support Objects
Tutorial 49: Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Tutorial 50: Support Parameterization from Analysis Services
Tutorial 51: Parameterization from Analysis Services – Cascading Picklists
Tutorial 52: Support Parameterization from Analysis Services – Parameter Defaults
Tutorial 53: Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I
Tutorial 54: Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part II
Tutorial 55: Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I
Tutorial 56: Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part II
Tutorial 57: Mastering OLAP Reports: Parameterized Grouping

Forum:

Microsoft MSDN RSS Forum: http://forums.microsoft.com/msdn/ShowForum.aspx?siteid=1&ForumID=82

Database Journal RSS Forum: Other:

http://www.15seconds.com/issue/041110.htm

1. Get total rows of table:
SELECT count(*) FROM <table_name>

2. Get total rows having field value null:
SELECT count(*) FROM <table_name> where <field_name> is null

3. Get column names of table:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = <table_name>

4. Get all rows of table:
SELECT * from <table_name>

5. Delete all rows of table (USE WITH CAUTION):
delete from <table_name>

6. Delete table (USE WITH CAUTION):
drop table <table_name>

7. Get reasons of job failure of SQL Agent jobs:
select * from MSDB.dbo.sysjobhistory

8. to search all tables to find a column or field name in a database

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name=’YourColumnName”