Temporary Tables

Temporary Table

create table #temptable {
 id int,
 name nvarchar(50)
};

select * into #temptable from UserTable;

Features

• Table name begin with '#'
• Table will be automatically dropped when session closed
• Manually drop table will be suggested
• Temporary table will be stored at database 'tempdb'
• Different session/user can create temporary table with same table name
• Index supported

Table Variables

declare @temptable table{
 id int,
 name nvarchar(50)
};

Features

• Table name begin with '@'
• Table do not need drop
• Table data only exist in memory
• Can not create from select statement

Paging on SQL Server 2012

SQL Server 2008-   Paging by row_number() function:

declare @JobID varchar(max);
declare @JobName nvarchar(max);
declare @CompanyName nvarchar(max);
declare @DataSource nvarchar(max);
declare @JobCategory nvarchar(max);
declare @Area nvarchar(max);
declare @PageIndex int;
declare @PageSize int;

set @PageIndex=1;
set @PageSize=20;

select
    d.*
from
(
    select
        ROW_NUMBER() over(order by JobName asc) RowIndex,  -- sortField, sortOrder
        v.*
    from JobDataView v
    inner join
    (
        select distinct JobID
        from JobDataSheet
        where
            (@JobID is null or JobID=@JobID)
        and (@JobName is null or JobName like '%'+@JobName+'%')
        and (@CompanyName is null or CompanyName like '%'+@CompanyName+'%')
        and (@DataSource is null or DataSource=@DataSource)
        and (@JobCategory is null or CategoryName=@JobCategory)
        and (@Area is null or (InfoName='Area' and InfoValue like '%'+@Area+'%'))
    ) s on s.JobID=v.JobID
) d
where d.RowIndex > (@PageIndex-1)*@PageSize and d.RowIndex <= @PageIndex*@PageSize;

SQL Server 2012   Paging by offset|fetch:

declare @JobID varchar(max);
declare @JobName nvarchar(max);
declare @CompanyName nvarchar(max);
declare @DataSource nvarchar(max);
declare @JobCategory nvarchar(max);
declare @Area nvarchar(max);
declare @PageIndex int;
declare @PageSize int;

set @PageIndex=1;
set @PageSize=20;

select
    v.*
from JobDataView v
inner join
(
    select distinct JobID
    from JobDataSheet
    where
        (@JobID is null or JobID=@JobID)
    and (@JobName is null or JobName like '%'+@JobName+'%')
    and (@CompanyName is null or CompanyName like '%'+@CompanyName+'%')
    and (@DataSource is null or DataSource=@DataSource)
    and (@JobCategory is null or CategoryName=@JobCategory)
    and (@Area is null or (InfoName='Area' and InfoValue like '%'+@Area+'%'))
) s on s.JobID=v.JobID
order by v.JobName asc
offset (@PageIndex-1)*@PageSize rows
fetch next @PageSize rows only

reference : http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx

DateTime format code

Style Code	Style	Format	Example
0 or 100	Default. Equivalent to not specifying a style code.	mon dd yyyy hh:mmAM	Sep 8 2007 9:00PM
1	USA date.	mm/dd/yy	09/08/07
2	ANSI date.	yy.mm.dd	07.09.08
3	UK / French date.	dd/mm/yy	08/09/07
4	German date.	dd.mm.yy	08.09.07
5	Italian date.	dd-mm-yy	08-09-07
6	Abbreviated month.	dd mmm yy	08 Sep 07
7	Abbreviated month.	mmm dd, yy	Sep 08, 07
8 or 108	24 hour time.	HH:mm:ss	21:00:00
9 or 109	Default formatting with seconds and milliseconds appended.	mon dd yyyy hh:mm:ss:fffAM	Sep 8 2007 9:00:00:000PM
10	USA date with hyphen separators.	mm-dd-yy	09-08-07
11	Japanese date.	yy/mm/dd	07/09/08
12	ISO date.	yymmdd	070908
13 or 113	European default with seconds and milliseconds.	dd mon yyyy HH:mm:ss:fff	08 Sep 2007 21:00:00:000
14 or 114	24 hour time with milliseconds.	HH:mm:ss:fff	21:00:00:000
20 or 120	ODBC canonical date and time.	yyyy-mm-dd HH:mm:ss	2007-09-08 21:00:00
21 or 121	ODBC canonical date and time with milliseconds.	yyyy-mm-dd HH:mm:ss.fff	2007-09-08 21:00:00.000
101	USA date with century.	mm/dd/yyyy	09/08/2007
102	ANSI date with century.	yyyy.mm.dd	2007/09/08
103	UK / French date with century.	dd/mm/yyyy	08/09/2007
104	German date with century.	dd.mm.yyyy	08.09.2007
105	Italian date with century.	dd-mm-yyyy	08-09-2007
106	Abbreviated month with century.	dd mmm yyyy	08 Sep 2007
107	Abbreviated month with century.	mmm dd, yyyy	Sep 08, 2007
110	USA date with hyphen separators and century.	mm-dd-yyyy	09-08-2007
111	Japanese date with century.	yyyy/mm/dd	2007/09/08
112	ISO date with century.	yymmdd	20070908
126	ISO8601, for use in XML.	yyy-mm-ddThh:mm:ss	2007-09-08T21:00:00

Query all columns of all tables

SELECT
    a.TABLE_NAME                as 表格名稱,
    b.COLUMN_NAME               as 欄位名稱,
    b.DATA_TYPE                 as 資料型別,
    b.CHARACTER_MAXIMUM_LENGTH  as 最大長度,
    b.COLUMN_DEFAULT            as 預設值,
    b.IS_NULLABLE               as 允許空值,
    (
        SELECT
            value
        FROM
            fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, 'column', default)
        WHERE
            name='MS_Description' 
            and objtype='COLUMN' 
            and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME
    ) as 欄位備註
FROM
    INFORMATION_SCHEMA.TABLES  a
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON ( a.TABLE_NAME=b.TABLE_NAME )
WHERE
    TABLE_TYPE in ('BASE TABLE','VIEW')
ORDER BY
    a.TABLE_NAME, ordinal_position

CTE, recursive search

WITH DepartmentSearch(DeptID, DeptParent, DeptName, OuID)
AS
(
    -- 找出簽核者所屬部門
    SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
    FROM tbDepartment d
    LEFT JOIN tbEmpDept ed ON ed.DeptID = d.DeptID
    INNER JOIN tbFlowApprove fa ON fa.Approver = ed.EmpID
    WHERE fa.FlowInsID = @FlowInsID AND fa.FlowAct = @FlowAct AND fa.ActAppendix IS NULL
    UNION ALL
    -- 遞迴向上找出主管部門
    SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
    FROM tbDepartment d
    INNER JOIN DepartmentSearch ON DepartmentSearch.DeptParent = d.DeptID
)
SELECT * FROM DepartmentSearch

other ref: 一般資料表運算式(Common Table Expressions, CTE)

for xml usage

SELECT
p.[product_name],
p.[product_imgstyle],
p.[product_color],
p.[product_size],
p.[product_weight],
p.[product_desc],
(
 SELECT l.[purchase_url], s.[store_name], s.[display_order]
 FROM [Product_StoreLink] l, [Store] s
 WHERE l.[store_id] = s.[store_id] AND l.[product_id] = p.[product_id]
 FOR XML RAW
) store
FROM Product p

Fill zero in front of number

SQL Server:

select replicate('0', (10-len('123')))+'123'

Oracle:

SELECT LPad('123',10,'0') FROM dual

DB2:

values char(repeat('0',10-length('123'))||'123',10)

SQL Server T-SQL Cheat Sheet

DECLARE and SET Varibales DECLARE @Mojo int SET @Mojo = 1 SELECT @Mojo = Column FROM Table WHERE id=1 IF / ELSE IF / ELSE Statement IF @Mojo < 1 BEGIN PRINT 'Mojo Is less than 1' END ELSE IF @Mojo = 1 BEGIN PRINT 'Mojo Is 1' END ELSE BEGIN PRINT 'Mojo greater than 1' END CASE Statement SELECT Day = CASE WHEN (DateAdded IS NULL) THEN 'Unknown' WHEN (DateDiff(day, DateAdded, getdate()) = 0) THEN 'Today' WHEN (DateDiff(day, DateAdded, getdate()) = 1) THEN 'Yesterday' WHEN (DateDiff(day, DateAdded, getdate()) = -1) THEN 'Tomorrow' ELSE DATENAME(dw , DateAdded) END FROM Table Add A Column ALTER TABLE YourTableName ADD [ColumnName] [int] NULL; Rename a Column EXEC sp_rename 'TableName.OldColName', 'NewColName','COLUMN'; Rename a Table EXEC sp_rename 'OldTableName', 'NewTableName'; Add a Foreign Key ALTER TABLE Products WITH CHECK ADD CONSTRAINT [FK_Prod_Man] FOREIGN KEY(ManufacturerID) REFERENCES Manufacturers (ID); Add a NULL Constraint ALTER TABLE TableName ALTER COLUMN ColumnName int NOT NULL; Set Default Value for Column ALTER TABLE TableName ADD CONSTRAINT DF_TableName_ColumnName DEFAULT 0 FOR ColumnName; Create an Index CREATE INDEX IX_Index_Name ON Table(Columns) Check Constraint ALTER TABLE TableName ADD CONSTRAINT CK_CheckName CHECK (ColumnValue > 1) DROP a Column ALTER TABLE TableName DROP COLUMN ColumnName;

Single Line Comments SET @mojo = 1 --THIS IS A COMMENT Multi-Line Comments /* This is a comment that can span multiple lines */ Try / Catch Statements BEGIN TRY -- try / catch requires SQLServer 2005 -- run your code here END TRY BEGIN CATCH PRINT 'Error Number: ' + str(error_number()) PRINT 'Line Number: ' + str(error_line()) PRINT error_message() -- handle error condition END CATCH While Loop DECLARE @i int SET @i = 0 WHILE (@i < 10) BEGIN SET @i = @i + 1 PRINT @i IF (@i >= 10) BREAK ELSE CONTINUE END CREATE a Table CREATE TABLE TheNameOfYourTable ( ID INT NOT NULL IDENTITY(1,1), DateAdded DATETIME DEFAULT(getdate()) NOT NULL, Description VARCHAR(100) NULL, IsGood BIT DEFAULT(0) NOT NULL, TotalPrice MONEY NOT NULL, CategoryID int NOT NULL REFERENCES Categories(ID), PRIMARY KEY (ID) ); User Defined Function CREATE FUNCTION dbo.DoStuff(@ID int) RETURNS int AS BEGIN DECLARE @result int IF @ID = 0 BEGIN RETURN 0 END SELECT @result = COUNT(*) FROM table WHERE ID = @ID RETURN @result END GO SELECT dbo.DoStuff(0)

Reference : SQL Server T-SQL Cheat Sheet

Group by Month/ Group by Week

T-SQL

-- Group by month
SELECT
    YEAR([ImportDate]) iYear,
    MONTH([ImportDate]) iMonth,
    SUM([SourceItemCount]) ItemCount,
    SUM([ImportItemCount]) ImportCount
FROM [RssFromOrgImportLog] WITH (NOLOCK)
GROUP BY YEAR([ImportDate]), MONTH([ImportDate])
ORDER BY iYear DESC, iMonth DESC

-- Group by week
SELECT
    DATEADD(dd, (DATEDIFF(dd, -53690, [ImportDate])/7) * 7, -53690) iWeek,
    SUM([SourceItemCount]) ItemCount,
    SUM([ImportItemCount]) ImportCount
FROM [RssFromOrgImportLog] WITH (NOLOCK)
GROUP BY DATEADD(dd, (DATEDIFF(dd, -53690, [ImportDate])/7) * 7, -53690)
ORDER BY iWeek DESC

Try catch in SQL Server 2005

BEGIN
    BEGIN TRANSACTION 
    BEGIN TRY
        // QUERY HERE
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
        ROLLBACK
    END CATCH
END

Detect column exist

Sql server:

-- Add new column if column not exists
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ColumnName' AND Object_ID = Object_ID(N'TableName'))
ALTER TABLE [TableName] ADD [ColumnName] VARCHAR(200)


-- Modify column name if new column not exists
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'NewColumnName' AND Object_ID = Object_ID(N'TableName'))
EXECUTE sp_rename N'TableName.OldColumnName', N'NewColumnName', 'COLUMN' 

Data Paging

Sql Server 2005

DECLARE @PageIndex INT;
DECLARE @PageSize INT;
SET @PageSize = 3;
SET @PageIndex = 1;

DECLARE @StartIndex INT;
DECLARE @EndIndex INT;
SET @StartIndex = @PageSize * (@PageIndex-1) +1;
SET @EndIndex = @StartIndex + @PageSize - 1;


WITH ProductEntires AS(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY [UpdateDate] DESC) AS RowIndex, 
        [ID], 
        [ProductName], 
        [ProductDescription], 
        [ProductImage1], 
        [ProductTitle1], 
        [VotedAmount] 
    FROM 
        [TblProduct] 
)

SELECT * 
FROM ProductEntires
WHERE
    [RowIndex] BETWEEN @StartIndex AND @EndIndex

Get the column name which is a primary key

Sql server

DECLARE @TableName VARCHAR(128)
SELECT @TableName = 'test_table'

SELECT c.COLUMN_NAME 
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE
    pk.TABLE_NAME = @TableName
    AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND c.TABLE_NAME = pk.TABLE_NAME
    AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

Get all table's name from db

Sql server

SELECT * FROM INFORMATION_SCHEMA.TABLES

Access

SELECT * FROM MSYSOBJECTS

MySQL

SHOW TABLES

Oracle

SELECT * FROM USER_OBJECTS

Paging

Sql server

Assume:

@PageSize is the row counts displayed on page

@CurrentPage is the page number

SELECT TOP @PageSize * FROM Table WHERE ID NOT IN
    (SELECT TOP @PageSize*@CurrentPage ID FROM Table ORDER BY ID DESC)
ORDER BY ID DESC

Oracle

Assume:

@MaxRowNum means the max row to query

@MinRowNum means the min row to query

SELECT * FROM 
    (SELECT rownum, * FROM
        (SELECT * FROM Table WHERE SomeColumn = Something ORDER BY SomeColumns)
    WHERE rownum <= @MaxRowNum)
WHERE rownum >= @MinRowNum

↑better performance on visit first serval pages

SELECT * FROM Table WHERE rowind IN
    (SELECT rid FROM
        (SELECT rownum rno, rowid rid FROM
            (SELECT rowid FROM Table WHERE SomeColumn = Something ORDER BY SomeColumns)
        WHERE rownum <= @MaxRowNum)
    WHERE rno >= @MinRowNum)

↑better performance on higher page numbers

SELECT * FROM 
    (SELECT owner, table_name, row_number() OVER(ORDER BY table_name) rownumber FROM dba_tables)
WHERE rownumber >= @MinRowNum AND rownumber <= @MaxRowNum

Select random row from database

Sql server

SELECT TOP 1 * FROM Table WHERE SomeColumn = something ORDER BY NEWID()

ACCESS

SELECT TOP 1 * FROM Table WHERE SomeColumn = something ORDER BY RND(ColumnName)

MySQL

SELECT * FROM Table ORDER BY RAND() LIMIT 1

Oracle

SELECT * FROM (SELECT * FROM Table ORDER BY dbms_random.value) WHERE rownum = 1;