Common SQL statement Records

1. Table

--Build Tableif object_id('Student') is  not NULLCreate TableStudent (IDint Identity(1,1) not NULL, Namenvarchar( -), Codenvarchar( -), Flagint default(0))--add constraints when building tables, table fields define foreign keys, calculated by functionCREATE TABLE [dbo].[A](    [Id] [int] IDENTITY(1,1) not NULL Primary Key,    [ClassId] [int]  not NULL Foreign Key References [dbo].[Class]([Id]) ,    [Name] nvarchar( -),    [Invoiceyear] int,    [Invoicemonth] int,    [Date]  as(dbo. Todatefromperiod (Invoiceyear,invoicemonth)) PERSISTED, Memonvarchar( -)NULL) --Add Columnsif  not exists(Select *  fromsyscolumnswhereId=object_id('Student') andName='ID')Alter TableStudentAddIdint Identity(1,1) not NULL--Add a PRIMARY KEY constraintif object_id('pk_student') is  not NULLALTER TABLEStudentDROP CONSTRAINT [Pk_approvalmatrix]GOALTER TABLEStudent with CHECK ADD  CONSTRAINT [Pk_approvalmatrix] PRIMARY KEY CLUSTERED(IDASC)GO--Delete a tableDELETE  from [Student]--clear the Identity valueDBCCCheckident (Student, Reseed,0)--Inserting DataIF  not EXISTS(SELECT *  from [Student] WHERENAME='Win')INSERT [Student]([Code],[Memo])VALUES(N'CA'N'Simon Deschenes')--Update DataUPDATE [dbo].[Student] SETCode= '001'  WHERENAME='Win'

--insert into requires that the target table Table2 must exist, because the target table Table2 already exists, so we can insert constants in addition to the fields Table1 the source table.
Insert into Table2 (field1,field2,...) Select Value1,value2,... from Table1

--select into requires that the target table Table2 not exist because the table Table2 is created automatically when inserting and copies the specified field data from Table1 to Table2.
SELECT Vale1, value2 to Table2 from Table1

Insert into and select into:http://www.cnblogs.com/freshman0216/archive/2008/08/15/1268316.html

2. Functions

2.1--Custom function, return date according to month

Use TEST
GO
SET ansi_nulls on
GO
SET quoted_identifier on
GO
Create function [dbo]. [Todatefromperiod] (@Year int, @Month int)
returns datetime
With SCHEMABINDING
as
-Returns The first of the month for the specified year and month.
begin
return DateAdd (mm, ((@Year -1900) *12) [email protected],0)
End

--Call the Custom function
SELECT dbo. [Todatefromperiod] (' + ', ' 1 ') as result

2.2. Table-valued function

2.2.1 Direct return table

Create function Getp (@id int)
Returns table as
Return select * FROM [Project] where [email protected]

2.2.2 Returning a custom table

Create function Getp ()
Returns @t table (id int, name varchar (50))
As
Begin
Insert INTO @t select P.id,p.name from Project p
Return
End

3. Determine if the object exists

3.1 Determine if the user exists and create

If user_id (' FAs ') is null

CREATE USER [FAs] without LOGIN with default_schema=[dbo]

Other related judgments reference http://www.cnblogs.com/fumj/archive/2012/07/15/2592558.html

4. Add user DDS to the role db_owner

EXEC sp_addrolemember ' db_owner ', ' DDS '

Role function https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/

Common SQL statement Records