常用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

 

14. 日期加减

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT ''

      , [country_code]

      , [call_center_code]

      , [cutoff_year]- 1

      , [cutoff_month]

      , DATEADD ( year ,- 1, [curr_cutoff_date])

      , [last_cutoff_date]

      , [updated_user]

  FROM [cigna_china_tt]. [dbo]. [cutoff_period]

 

/****** Script for SelectTopNRows command from SSMS  ******/

insert [cutoff_period]( [country_code], [call_center_code], [cutoff_year], [cutoff_month], [curr_cutoff_date],

[last_cutoff_date], [updated_user])

SELECT

      [country_code]

      , [call_center_code]

      , [cutoff_year]- 1

      , [cutoff_month]

      , DATEADD ( year ,- 1, [curr_cutoff_date])

      , DATEADD ( year ,- 1, [last_cutoff_date])

 

      , [updated_user]

  

  FROM [cigna_china_test]. [dbo]. [cutoff_period] where cutoff_year = '2010'

 

  select * from [cutoff_period]

 

  SELECT ''

      , [country_code]

      , [call_center_code]

      , [cutoff_year]- 1

      , [cutoff_month]

      , DATEADD ( year ,- 1, [curr_cutoff_date])

      , [last_cutoff_date]

      , [updated_user]

  FROM [cigna_china_tt]. [dbo]. [cutoff_period]

 

15.fe

DUMP     TRANSACTION     库名      WITH     NO_LOG        

 

16. 收缩日志

ALTER DATABASE ATDB

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (ETN_Log, 1);

GO

-- Reset the database recovery model.

ALTER DATABASE ATDB

SET RECOVERY FULL ;

GO

 

17. 行转列

select * from

(

  select country_code, call_center_code, score_type, score, updated_user, updated_datetime

  from kpi_score

) as k pivot ( max ( score) for score_type in( [0], [1], [2])) as t

 

18. convert

  select CONVERT(varchar, getdate(),103)

 

 

 

delete t
            from (select a.seller_id,a.employee_id,
            row_number() over (partition by a.seller_id order by a.seller_id) as r
            from if_employee_info_dump a) t
            where t.r > 1