sql server 我常用的语句

1. computed column 

alter table tableName add columnName as (cast(aColumn as float) / bColumn * 100) persisted; 

 

2. unique nullable 

create unique nonclustered index[UniqueName] on [tableName]([columnNameA] asc) where ([columnNameA] is not null);

 

3. foreign key cascade delete 

alter table [tableNameA] drop constraint [FKName];
alter table [tableNameA] with check add constraint [FKName] foreign key(foreignColumnName) references [tableNameB]([Id]) on delete cascade -- or on delete set null;

 

4. reset auto increment 

DBCC CHECKIDENT ('TableName') --check current
DBCC CHECKIDENT ('TableName', RESEED, 0); --reset to 0 , next is 1

 

5. create index 

create nonclustered index [indexName] on [tableName]([columnName] asc);

 

6. select Ids to string list 

declare @string nvarchar(MAX);
select @string = coalesce(@string + ', ', '') + cast(Id as nvarchar(5))
from Products;
select @string;

 

6.01. delay

declare @delayLength char(8) = '00:00:10';
waitfor delay @DelayLength

 

6.02 convert time zone 

SELECT CONVERT(DATETIME, CreatedAt) AT TIME ZONE 'UTC' AT TIME ZONE 'Singapore Standard Time' FROM HangFire.Job;

 

7. 找 parent

--找 parent
GO
declare @table nvarchar(128) = 'CustomerDeliveryAddresses';
SELECT  
    STRING_AGG (obj.name, ',') as FK_NAME,
    --sch.name AS [schema_name],
    --tab1.name AS [table],
    tab2.name AS [referenced_table],
    STRING_AGG (col1.name, ',') as foreignKeys,
    STRING_AGG (col2.name, ',') as principalKeys
    --col1.name AS foreignKey,
    --col2.name AS principalKey
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id 
where tab1.name = @table and col1.name != 'createdBy' and col1.name != 'lastModifiedBy' group by tab2.name
GO

 

8. 找 child

GO
--找 children
DECLARE @table nvarchar(128) = 'TeamMember';
 
SELECT  
    STRING_AGG (obj.[name], ',') as FK_NAME,
    --sch.name AS [schema_name],
    --tab1.name AS [table],
    tab2.name AS [referenced_table],
    STRING_AGG (col1.[name], ',') as foreignKeys,
    STRING_AGG (col2.[name], ',') as principalKeys
    --col1.name AS foreignKey,
    --col2.name AS principalKey
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = referenced_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.parent_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = parent_column_id AND col2.object_id = tab2.object_id 
where tab1.name = @table and col1.name != 'createdBy' and col1.name != 'lastModifiedBy' group by tab2.name
GO

 

 

 

 

大杂烩

 
use [simple];

drop proc dbo.performance_tuning_createRandomData;
-- create procedure 
go
    create proc dbo.performance_tuning_createRandomData
    (
        @name nvarchar(100),
        @email nvarchar(100),
        @outValue nvarchar(100) output
    )
    as 
    begin 
        set nocount on;
        set @outValue = N'output ok';
        declare @value nvarchar(max) = N'dada';
        set @value = N'super';
        --print @value;
        insert into dbo.test (name,email) values (@name,@email);
    end
go
go
    declare @outValue nvarchar(100);
    EXEC dbo.performance_tuning_createRandomData N'mk100', N'hengkeat87@gmail.com', @outValue output;  
    print @outvalue;
go

-- create function 
drop function dbo.performance_tuning_randomString;
go
    create function dbo.performance_tuning_randomString         
    (
        @name nvarchar(100)
    )
    returns nvarchar(50)
    as 
    begin
        return 'value'; 
    end
go
select dbo.performance_tuning_randomString('dada'); 


-- declare and set value
go 
    declare @value nvarchar(max) = N'dada';
    set @value = N'super';
    -- select @value = count(*) from @table;
    print @value;
go


-- if else, begin end 不一定需要
go
    declare @value2 nvarchar(30) = 'keatkeat2';
    if(@value2 = 'keatkeat')
        begin
            print 'yes';
        end 
    else
        begin
            print 'no';
        end
go


-- for loop
go
    declare @i int = 0;
    while(@i < 10)
    begin
        print @i;
        set @i = @i + 1;
    end 
go

-- random str + number and random number
go
     
     SELECT CONVERT(INT, 5 * RAND())
     SELECT SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 9)
     select NEWID();
go


-- create temp table and loop it 
go
    declare @temp table (name nvarchar(max), [index] int identity(1,1));
    insert into @temp (name) values ('keatkeat'), ('xinyao');

    declare @i int = 0;
    declare @length int;
    select @length = count(*) from @temp;
    while(@i < @length)
    begin
        declare @value nvarchar(max)
        select @value = name from @temp order by [index] offset @i rows fetch next 1 rows only;
        print @value;
        set @i = @i + 1;
    end   
go


-- copy table to other table (cross database)
-- copy table to temp table 
go
    insert into test (email,[name]) select email,[name] from performance_tuning.dbo.Products where Id = 1;

    select Id,[name] into #temp from test order by Id;
    declare @i int = 0;
    declare @length int;
    select @length = count(*) from #temp;
    print @length;
    while(@i < @length)
    begin
        declare @value nvarchar(max)
        select @value = [name] from #temp order by Id offset @i rows fetch next 1 rows only;
        print @value;
        set @i = @i + 1;
    end  
    drop table #temp;
go


-- random enum 
go
    declare @values table([value] nvarchar(25));
    insert into @values values ('John'),('Sarah'),('George');
    select top 1 * from @values order by newid(); --or rand()     
go

 

杂会 2 

--dynamic sql
declare @table nvarchar(max) = 'table';
declare @value nvarchar(max) = 'value';
--input
declare @count int;
--output
declare @sql nvarchar(max) = N'select @count = count(*) from ' + @table + ' where column = @value';
exec sp_executesql 
            @sql, 
            N'@count INT OUT, @value nvarchar(max)', 
            @value = @value,
            @count = @count output;
print @count;


-- create temp table to store data
go
create table #tempTable
(
    Id int
);
declare @sql nvarchar(max) = N'insert into #tempTable (Id) select Id from Questions';
EXEC(@sql);
select *
from #tempTable;
drop table #tempTable;
go

 

dynamic table name 

declare @count int;
declare @sql nvarchar(max) = N'select @count = count(*) from ' + @name;
exec sp_executesql @sql, N'@count int out', @count out
print @count;