MSSQL练习语句

 USE [NikeLeave]
GO
/****** 对象:  Table [dbo].[SPECIAL_HOLIDAY]    脚本日期: 11/17/2008 10:06:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SPECIAL_HOLIDAY](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [fromDate] [datetime] NOT NULL,
 [toDate] [datetime] NOT NULL,
 [status] [varchar](50) NOT NULL,
 [description] [ntext] NULL,
 CONSTRAINT [PK__SPECIAL_HOLIDAY__0DAF0CB0] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

select newid()

select top 1 * from [dbo].[SPECIAL_HOLIDAY] order by newid()

select name from sysobjects where type='U'

select top 5 * from (select top 15 * from [dbo].[SPECIAL_HOLIDAY] order by id asc) sholiday order by id desc

SELECT IS_SRVROLEMEMBER('sysadmin')

SELECT IS_MEMBER('db_owner')

select * from(
select fromDate,ROW_NUMBER()OVER(orderby toDate) as row from [dbo].[SPECIAL_HOLIDAY]
) a
where row between 12 and 18

EXEC master..xp_cmdshell 'bcp NikeLeave.dbo.SPECIAL_HOLIDAY out c:/Temp.xls -c -q -S "10.30.31.64" -U "sa" -P "Everse2005"'

EXEC master..xp_cmdshell 'bcp NikeLeave.dbo.SPECIAL_HOLIDAY out c:/Temp.txt -c -q -S "10.30.31.64" -U "sa" -P "Everse2005"'

EXEC master..xp_cmdshell 'bcp NikeLeave.dbo.SPECIAL_HOLIDAY in c:/Temp.xls -c -q -S "10.30.31.64" -U "sa" -P "Everse2005"'

EXEC master..xp_cmdshell 'bcp NikeLeave.dbo.SPECIAL_HOLIDAY in c:/Temp.txt -c -q -S "10.30.31.64" -U "sa" -P "Everse2005"'

delete  from NikeLeave.dbo.SPECIAL_HOLIDAY where id in (select max(id) from NikeLeave.dbo.SPECIAL_HOLIDAY group by fromDate,toDate,status)

declare @shId as int
set @shId= 45
select * from NikeLeave.dbo.SPECIAL_HOLIDAY where id = @shId

select * from NikeLeave.dbo.SPECIAL_HOLIDAY where id = 45

with c1 as
(select year(fromDate) as fromYear from NikeLeave.dbo.SPECIAL_HOLIDAY ),
c2 as
(select fromYear as nextYear from c1 where fromYear >2008)
select nextYear from c2

/**open try...catch gongneng
SET XACT_ABORT ON
**/

 

CREATE FUNCTION HCnt()
RETURNS INT
AS
BEGIN
RETURN(
SELECT COUNT(*) AS 'Holiday H Count'
FROM [dbo].[SPECIAL_HOLIDAY]
WHERE status = 'H'
)
end

create procedure findH
as
begin
select COUNT(*) AS 'Holiday H Count'
FROM [dbo].[SPECIAL_HOLIDAY]
where status = 'H'
end

CREATE ENDPOINT Orders_Endpoint
state=started
as http(
path='/sql/orders',
AUTHENTICATION=(INTEGRATED),
ports=(clear)
)
for soap(
WebMethod 'findH'(
name='NikeLeave.dbo.findH'
),

wsdl=default,
database='NikeLeave',
namespace='http://mysite.org/'
)