Tag: mssql

Add a auto_increment primary key

ALTER TABLE MyTable ADD mytableID int NOT NULL IDENTITY (1,1) PRIMARY KEY
More precisely to set a named table level constraint
ALTER TABLE MyTable ADD MytableID int NOT NULL IDENTITY (1,1), ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID)

Sha1()

sys.fn_varbintohexsubstring(0, HashBytes('SHA1', '1234561'), 1, 0) 

Alter

Add Column

ALTER TABLE dbo.YourTable ADD ID INT IDENTITY
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY(ID)

Change Column Type

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Key

Get all key with column_name

select * From INFORMATION_SCHEMA.KEY_COLUMN_USAGE where column_name=’column_name’

Drop specify key

 

alter table table_name drop constraint  PK_xxx;

Drop Table

DELETE FROM [arcas].[dbo].[flm_order];
DBCC CHECKIDENT ('arcas.dbo.[flm_order]',RESEED, 0)

分頁

有排序，又稱【夾擠式分頁查詢】。MS-SQL 2000以前，常見的分頁用語法。

SELECT * FROM 
(
    SELECT TOP [每頁長度] * FROM 
    (
        SELECT TOP [每頁長度*第幾頁] [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
        ORDER BY [排序欄位] [DESC/ASC]
    )
    AS [資料表别名1]
    ORDER BY [排序欄位] [ASC/DESC]
)
AS [資料表别名2] 
ORDER BY [排序欄位] [DESC/ASC]

 

無排序，在關聯式查詢時，可能導致每次查詢同一分頁的資料時，出來的資料都不一樣。

SELECT [欄位1, 欄位2, ...] FROM [資料表]
    WHERE [主要索引欄位] IN
    (
        SELECT * FROM 
        (
            SELECT TOP [每頁長度*第幾頁] [主要索引欄位]
                FROM [資料表]
                WHERE [篩選條件]     
        )
        AS [資料表别名1]
        WHERE [主要索引欄位] NOT IN
        (
            SELECT TOP [每頁長度] [主要索引欄位] 
                FROM [資料表]
                WHERE [篩選條件] 
        )
    )

 

MS-SQL 2005以後才有RANK()語法。

SELECT * FROM 
(
    SELECT rank() OVER ( ORDER BY [排序欄位] [ASC/DESC] ) AS RankNumber, * FROM
    (
        SELECT [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
    )
    AS [資料表别名1]
) AS [資料表别名2]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁]

 

MS-SQL 2000可用，但Select時，不可包含PK用的欄位。（需透過暫存資料表操作）

SELECT IDENTITY(INT,1,1) AS RankNumber, [欄位1, 欄位2, ...] 
INTO [#暫存資料表名稱] 
FROM [資料表]
ORDER BY [排序欄位] [ASC/DESC];
 
SELECT * FROM [#暫存資料表名稱]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁];
 
DROP TABLE [#暫存資料表名稱];

MYSQL的LIMIT效果

SQL SERVER 2000

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

 

( SQL SERVER 2005)

USE AdventureWorks; GO WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 10 AND 20;