SQL

SELECT COLUMN_NAME, COLLATION_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'schema_name' 
AND TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name'

-- example
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_name in ('LicenseFormHeader','LicenseFormLine') and table_schema = 'license'

//條件加
v.ProductCode = i.InvItemNo  collate Chinese_PRC_CI_AS

ALTER TABLE table_name
ALTER COLUMN column_name [data_type]
COLLATE Chinese_PRC_CI_AS

CREATE TABLE table_name (
    column1_name [data_type] COLLATE Chinese_PRC_CI_AS,
    column2_name [data_type] COLLATE Chinese_PRC_CI_AS,
    ...
)

SELECT t.NAME AS TableName,SCHEMA_NAME(t.schema_id) as ServiceName,
(SUM(a.total_pages) * 8) / 1024.0 as TotalSpaceMB, 
(SUM(a.total_pages) * 8) / 1024.0 * 100 / (SELECT SUM(total_pages) * 8 / 1024.0 FROM sys.allocation_units) as Percentage
FROM sys.tables t 
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id 
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id 
WHERE i.OBJECT_ID > 255 AND i.index_id IN (0,1) 
GROUP BY t.NAME ,t.schema_id
ORDER BY TotalSpaceMB DESC

ALTER TABLE  [self].[ModelEntryLog]
ADD  ApiLogId   INT IDENTITY(1,1)
CONSTRAINT PK_ModelEntryLog PRIMARY KEY CLUSTERED

ALTER TABLE [self].[ModelEntryLog]
DROP CONSTRAINT PK__ModelEnt__F842F02069FD48C7

ALTER TABLE  [self].[ModelEntryLog] ADD PRIMARY KEY (ApiLogId)

ALTER TABLE [dbo].[TableName] ALTER COLUMN [dbo.ID] BIGINT

CREATE TABLE MyTable
(   seq Bigint IDENTITY(1,1),
    MyColumn VARCHAR(50) COLLATE Chinese_PRC_CI_AS
    MyOtherColumn NVARCHAR(100) COLLATE Chinese_PRC_CI_AS
    PRIMARY KEY (ID)
)

SELECT * FROM Table WHERE convert(date,[timestamp]) = '2003-02-15'

SELECT TOP (100) * FROM [dpl].[WhiteListDetail] WHERE convert(date,LastUpdatedDate) = DATEADD(DAY,-1, convert(date,GETUTCDATE()))

CAST(LastUpdatedDate AT TIME ZONE 'UTC' AS DATE)

SELECT MAX(CAST(LastUpdatedDate AT TIME ZONE 'UTC' AS DATE))

--分組排序
select ROW_NUMBER() OVER (PARTITION BY address ORDER BY id ASC) as ROW_ID ,* from Customers

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2, column3 ORDER BY lastupdated DESC) AS rn
    FROM table_name
)
DELETE FROM CTE WHERE rn > 1;

SELECT r.session_id,
    r.status AS [指令狀態],
    r.command AS [指令類型],
    r.wait_time/1000.0 AS [等待時間(秒)],
    s.client_interface_name AS [連線資料庫的驅動程式],
    s.host_name AS [電腦名稱],
    s.program_name AS [執行程式名稱],
    t.text AS [執行的SQL語法],
    r.blocking_session_id AS [被鎖定卡住的session_id]
    FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE s.is_user_process = 1;

EXEC sp_who2;

DECLARE @id INT;
DECLARE @count INT = 1;

WHILE @count > 0
BEGIN
    SELECT TOP 1 @id = id FROM table_name WHERE condition;
    SET @count = @@ROWCOUNT;
    IF @count > 0
    BEGIN
        DELETE FROM table_name WHERE id = @id;
    END
END

SELECT TOP 10
    total_worker_time/execution_count AS AvgCPU,
    total_logical_reads/execution_count AS AvgReads,
    total_logical_writes/execution_count AS AvgWrites,
    execution_count,
    total_elapsed_time/execution_count AS AvgElapsedTime,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time DESC;

CREATE INDEX IX_Name
ON  table ( field);

with temp as (

)select temp.* from sublog join on where exists(

)