常用T-sql

1.create table
create table dbo.app_param(
    sn int identity(1,1) not null,
    type nvarchar(50) not null,
    module nvarchar(50) not null,
    ckey nvarchar(50) not null,
    cvalue nvarchar(50) not null,
    remark nvarchar(50) null,
    updated_user nvarchar(20) null,
    created_datetime datetime null,
    updated_datetime datetime null,
    constraint PK_app_param primary key nonclustered(sn asc)
)


2.create index
create unique clustered index IX_app_param on dbo.app_param
(
    type asc,
    module asc,
    ckey asc
)


3.create foreign key
alter table dbo.app_config add constraint FK_app_config_call_center
foreign key( country_code, call_center_code)
references dbo.call_center(country_code, call_center_code)

4.set null

5. add column
alter table employee add subsidy_status smallint default 1 null
alter table employee_comp_detail add subsidy decimal(16,2) null

6.three common lines
updated_user nvarchar(20)  null,
created_datetime datetime default getdate() not null,
updated_datetime datetime default getdate() not null,

7. rename column
exec sp_rename 'kpi_callcenter.active', 'is_active', 'column';

8. change column type
alter  table call_center_incentive  alter column is_active  smallint

9.drop column
alter table employee drop column employee_name_th

10.drop PK
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint

11.drop index
DROP index anp_comm_rating.IX_anp_comm_rating

7. rename table
exec sp_rename 'table1', 'table2';

8.dump_history_log table

create table if_salespermonth_dump(
    employee_id nvarchar(20) null,
    month_year    nvarchar(20) null,
    sales_per_month nvarchar(20) null
)

create table if_salespermonth_history(
    sn int identity(1,1) not null,
    employee_id nvarchar(20) null,
    month_year    nvarchar(20) null,
    sales_per_month nvarchar(20) null,
    created_datetime datetime default getdate() not null,
    constraint PK_if_salespermonth_history primary key clustered(sn asc)
)

CREATE TABLE [dbo].if_salespermonth_log(
    [sn] [int] IDENTITY(1,1) NOT NULL,
    employee_id nvarchar(20) null,
    month_year    nvarchar(20) null,
    sales_per_month nvarchar(20) null,
    [transaction_date] [datetime] NOT NULL,
    [batch_no] [int] NOT NULL,
    [execution_date] [datetime] NOT NULL,
    [error_type] [nvarchar](10) NOT NULL,
    [error_msg] [nvarchar](200) NULL,
    [updated_user] [nvarchar](20) NULL,
    [created_datetime] [datetime] default getdate() NOT NULL,
    [updated_datetime] [datetime] default getdate() NOT NULL,
 CONSTRAINT [PK_if_salespermonth_log] PRIMARY KEY CLUSTERED (sn asc)
)



11.建表的4行
sn int identity(1,1) not null,
    updated_user nvarchar(20)  null,
    created_datetime datetime default getdate() not null,
    updated_datetime datetime default getdate() not null,
    constraint PK_c_commission_rate primary key nonclustered(sn asc)


13.生成数据字典的sql：select tname,colid,cname,ctype,length =

case ctype
    when 'nvarchar' then length/2
    when 'nchar' then length/2
    else length
    end,


xprec,xscale,isnullable from all_col where tname not like 'if%' and tname not like 'cn%'
 order by 1,2
 
 select * from all_col  order by 1,2


select row_number() over (partition by employee_id order by program_id) as row_num,
      *
from employee_program