sql积累
--exec sp_addlinkedserver
--@server='DB',
--@srvproduct='',
--@provider='SQLNCLI',
--@datasrc='192.168.0.1',
--@location=null,
--@provstr=null,
--@catalog=null
--
--exec sp_addlinkedsrvlogin 'db','false',null,'name','pwd'
--
--select * from db.table
--
--
--exec sp_dropserver 'db','droplogins'
删除远程链接服务器
Exec sp_droplinkedsrvlogin tg,Null
exec sp_dropserver 'tg'
增加 windows 帐户
EXEC sp_grantlogin 'WWW-458BD01F1ED\IUSR_WWW-458BD01F1ED'
启用Ad Hoc Distributed Queries：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
使用完成后，关闭Ad Hoc Distributed Queries：
sp_configure 'Ad Hoc Distributed Queries', 0;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
SELECT *
FROM openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;hdr=Yes;DataBase=some.xls',test$)
declare @T table (ID int,pid int,NAME varchar(6))
insert into @T
select 1,0,'上衣' union all
select 2,0,'鞋子' union all
select 3,0,'裤子' union all
select 4,1,'毛衣' union all
select 5,1,'衬衫' union all
select 6,2,'球鞋' union all
select 7,2,'皮鞋' union all
select 8,3,'西裤' union all
select 9,3,'筒裤' union all
select 10,4,'羊毛衣' union all
select 11,4,'牛毛衣' union all
select 12,5,'白衬衫' union all
select 13,5,'黑衬衫'
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=192.168.0.0;User ID=name;Password=pwd'
).database.db.table
用事务日志来恢复Update、Delete误操作引起的数据丢失
方法一（只有 sysadmin 固定服务器角色的成员才能执行以下操作）：
1如果误操作之前存在一个完全备份（或已有多个差异备份或增量备份），首先要做的事就是进行一次日志备份（如果为了不让日志文件变大而置trunc. log on chkpt.选项为1那就没有办法了）
backup log dbname to disk='filename'
执行后在你mssql目录的backup文件夹里就有一个文件，名叫：filename
2恢复一个完全备份，注意需要使用with norecovery，如果还有其他差异或增量备份，则逐个恢复。
restore database dbname from disk='filename' with norecovery（,move…）
当然如果在恢复之前你mssql里的data文件夹里有同名的.mdf或.ldf文件，你要先执行sp_detach_db 'dbname'，然后将同名的.mdf或.ldf文件剪切出data文件夹，不然会报错；或者你在restore database时用move参数(restore filelistonly from disk=’以前文件备份的路径和名称’，得到逻辑文件名，move ‘逻辑文件名’ to ‘一个新路径’)，将物理文件（.mdf或.ldf）存放到不同的文件夹。
3恢复最后一个日志备份即刚做的日志备份，指定恢复时间点到误操作之前的时刻
restore log dbname from disk='filename'
with recovery,stopat='date_time'
这里的date_time时间格式用datetime格式。
当然，如果误操作是一些不记日志的操作比如truncate table，select into等操作，那么是无法利用上述方法来恢复数据的。
--下面演示了如何恢复数据
--首先要备份当前日志
BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT,NO_TRUNCATE
--利用文件组备份恢复破坏的文件
RESTORE DATABASE db FROM DISK='c:\db.bak' WITH NORECOVERY
--还原到日志点
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
--显示已经恢复的数据
SELECT COUNT(*) FROM db.dbo.ta
SELECT COUNT(*) FROM db.dbo.tb
GO
--删除测试
DROP DATABASE db
CREATE TABLE [dbo].[tb22](
[id] [int] IDENTITY(1,1) NOT NULL,
[编号] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[A] [int] NULL,
[B] AS ([dbo].[f_d]([id],[编号]))
) ON [PRIMARY]
http://bbs.cnw.com.cn/thread-177813-1-1.html 备份还原
非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：
基础表的数据行不按非聚集键的顺序排序和存储。
非聚集索引的叶层是由索引页而不是由数据页组成。
NULL>0的值为UNKNOWN, NOT UNKNOWN的值还为UNKNOWN.UNKNOWN or TRUE的值为TRUE
SET STATISTICS IO ON/OFF
SET SHOWPLAN_ALL ON/OFF
Clustered Index Scan和Clustered Index Seek的区别?
Index Seek means that SQL Server will traverse the index from the root down to the leaf level, comparing the values in the SARG to the key values in the index rows to determine which page to look at next. Seeking through an index typically means a root-to-leaf traversal.
A scan, the alternative to Index Seek, means that SQL Server will stay at the leaf level and traverse just that one level of the index from the first page to the last. I like to think of a seek as a vertical traversal of an index and a scan as a horizontal traversal. Remember that for a clustered index, the leaf level of the index is the table data itself, so scanning the clustered index really means scanning the table. In fact, the only time you'll see Table Scan as an operator in the plan is for heaps.
124.248.
1038#JDM01
sp_spaceused tablename 查看表的数据情况
PATINDEX ( '%pattern%', expression )
对于所有有效的文本和字符数据类型，返回指定表达式中模式第一次出现的起始位置，如果未找到模式，则返回零。
SET IDENTITY_INSERT xxxLog ON
允许将显式值插入表的标识列中。
http://technet.microsoft.com/zh-cn/office/ms175913.aspx
sql执行计划图标解释
发送邮件
EXEC master.dbo.xp_sendmail
@recipients=N'danw@Adventure-Works.com',
@message=N'The master database is full.' ;
SQL Server 并没有内置邮件服务器（Mail Server），它跟我们发送邮件一样，需要用户名和密码通过 SMTP（Simple Message Transfer Protocol）去连接邮件服务器。我们想让 SQL Server 来发送邮件，首先要告诉它用户名称，密码，服务器地址，网络传送协议，邮件服务器的端口。。。等信息。
以下脚本实现了数据库邮件的配置：
--下面是具体的配置邮件步骤
--在 sa 系统帐户下运行。
--
--1. 启用 SQL Server 2005 邮件功能。
use master
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'Database mail XPs',1
go
reconfigure
go
--2. 在 SQL Server 2005 中添加邮件帐户（account）
exec msdb..sysmail_add_account_sp
@account_name = 'jgj' -- 邮件帐户名称（SQL Server 使用）
,@email_address = [email='jiaguijun@trusee.com']'jiaguijun@trusee.com'[/email] -- 发件人邮件地址
,@display_name = null -- 发件人姓名
,@replyto_address = null
,@description = null
,@mailserver_name = '203.86.70.229' -- 邮件服务器地址
,@mailserver_type = 'SMTP' -- 邮件协议（SQL 2005 只支持 SMTP）
,@port = 25 -- 邮件服务器端口
,@username = [email='jiaguijun@trusee.com']'jiaguijun@trusee.com'[/email] -- 用户名
,@password = 'xxxxxx' -- 密码
,@use_default_credentials = 0
,@enable_ssl = 0
,@account_id = null
--3. 在 SQL Server 2005 中添加 profile
exec msdb..sysmail_add_profile_sp @profile_name = 'dba_profile' -- profile 名称
,@description = 'dba mail profile' -- profile 描述
,@profile_id = null
-- 在 SQL Server 2005 中映射 account 和 profile
exec msdb..sysmail_add_profileaccount_sp @profile_name = 'dba_profile' -- profile 名称
,@account_name = 'jgj' -- account 名称
,@sequence_number = 1 -- account 在 profile 中顺序
--5. 利用 SQL Server 2005 Database Mail 功能发送邮件。
exec msdb..sp_send_dbmail @profile_name = 'dba_profile' -- profile 名称
,@recipients = [email='jiaguijun@trusee.com']'jiaguijun@trusee.com'[/email] -- 收件人邮箱
,@subject = 'SQL Server 2005 Mail 测试' -- 邮件标题
,@body = 'Hello Mail!测试' -- 邮件内容
,@body_format = 'TEXT' -- 邮件格式
--6. 查看邮件发送情况：
use msdb
go
select * from sysmail_allitems
select * from sysmail_mailitems
select * from sysmail_event_log
--如果不是以 sa 帐户发送邮件，则可能会出现错误：
--
--Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
--EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
--
--这是因为，当前 SQL Server 登陆帐户（login），在 msdb 数据库中没有发送数据库邮件的权限，需要加入 msdb 数据库用户，并通过加入 sp_addrolemember 角色赋予权限。假设该SQL Server 登陆帐户名字为 “dba”
--
--use msdb
--go
--
--create user dba for login dba
--go
--
--exec dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',
-- @membername = 'dba'
--go
--
--此时，再次发送数据库邮件，仍可能有错误：
--
--Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119
--profile name is not valid
--
--虽然，数据库用户 “dba” 已经在 msdb 中拥有发送邮件的权限了，但这还不够，他还需要有使用 profile：“dba_profile” 的权限。
--
--use msdb
--go
--
--exec sysmail_add_principalprofile_sp @principal_name = 'dba'
-- ,@profile_name = 'dba_profile'
-- ,@is_default = 1
--
--从上面的参数 @is_default=1 可以看出，一个数据库用户可以在多个 mail profile 拥有发送权限。
删除指定长度的字符并在指定的起始点插入另一组字符。
STUFF ( character_expression, start, length, character_expression )
--管好sql的用户,防止访问他不该访问的数据库(总控制,明细还可以控制他对于某个数据库的具体对象具有的权限)
--切换到你新增的用户要控制的数据库
use 你的库名
go
--新增用户
exec sp_addlogin 'test' --添加登录
exec sp_grantdbaccess N'test' --使其成为当前数据库的合法用户
exec sp_addrolemember N'db_owner', N'test' --授予对自己数据库的所有权限
--这样创建的用户就只能访问自己的数据库,及数据库中包含了guest用户的公共表
go
--删除测试用户
exec sp_revokedbaccess N'test' --移除对数据库的访问权限
exec sp_droplogin N'test' --删除登录
---------------------------------------------------------------------------
最后一步,为具体的用户设置具体的访问权限,这个可以参考下面的最简示例:
--添加
--添加用户:
exec sp_addlogin '用户名','密码','默认数据库名'
--添加到数据库
exec sp_grantdbaccess '用户名','数据库名'
--分本权限
grant insert,select,update,delete on table1 to public
SELECT *
FROM fn_helpcollations()
order by col collate chinese_prc_cs_as_ks_ws
查看 MS-SQL Tempdb 所占空间大小。
exec sp_helpdb tempdb
EXECUTE sp_executesql
