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.日期加减
/****** Scriptfor 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]
?
/****** Scriptfor 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
(
?selectcountry_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
?
?
?
?