SqlServer学习(三)【高级知识】

---

---

---

sqlserver常用sql语句，更改字段，建立唯一键，多个字段去重复等

--修改字段类型：
--alter table 表名 alter column 待修改字段名 待修改字段类型
alter table users alter column userid varchar(10)

--多个字段建立唯一索引
create unique index [索引名] on 表名(字段名,字段名)
alter table 表名 add constraint 约束名 unique (字段名)
alter table 表名 add constraint 约束名 unique (字段名,字段名)

--创建表的时候创建自增主键
create table tb(id int identity(1,1) primary key ) 

--修改字段主键  自增进入ssms界面设置
alter table tb alter column id int not null 
alter table tb add constraint pkid primary key (id)

--修改字段为非空
alter table tb alter column id int not null 

--建立索引
create index mycolumn_index on mytable (myclumn) 
--AmazonOrderId和seller_id两个字段重复
SELECT count(*) 
FROM (
         SELECT rn = ROW_NUMBER()OVER(PARTITION BY AmazonOrderId, seller_id ORDER BY GETDATE()), * 
         FROM Orders
     ) t
WHERE rn = 1

---

查询数据库表记录数的最快方法

在查询一张表的记录数时，由于记录数太多，采用"Select count(1) from 表名"进行查询一直超时。

后采用下面的SQL，不到1秒钟即返回结果。

select rowcnt from sysindexes where id=object_id('表名')
--------------------------------------------------------------
## SQLServer通过sql修改表主键
先查询出主键的内码，然后删除该主键

```sql
DECLARE @NAME SYSNAME
DECLARE @TB_NAME SYSNAME
	SET @TB_NAME = '表名'
	SELECT TOP 1 @NAME = NAME FROM SYS.OBJECTS WITH(NOLOCK)
WHERE TYPE_DESC ='PRIMARY_KEY_CONSTRAINT' AND PARENT_OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WITH(NOLOCK) WHERE NAME = @TB_NAME )
SELECT @NAME
	DECLARE @ALTERSQL NVARCHAR(MAX)
SET @ALTERSQL=N'ALTER TABLE '+@TB_NAME+' DROP CONSTRAINT '+@NAME
	EXEC SP_EXECUTESQL @ALTERSQL;
GO

然后再添加主键

ALTER TABLE 表名 ADD PRIMARY KEY (主键1, 主键2)
GO

SQL Server表的数据量大小查询

然后在网上找了找SQL脚本，看能不能查看下哪个表的数据量那么大。

网上找到的脚本：

CREATE TABLE #Temp (NAME nvarchar(100),ROWS char(20),reserved varchar(18) ,Data varchar(18) ,index_size varchar(18) ,Unused varchar(18) )
GO
INSERT #Temp EXEC SP_MSFOREACHTABLE 'EXEC sp_spaceused "?"'

SELECT * FROM #Temp ORDER BY CONVERT(INT,REPLACE(DATA,'KB','')) DESC

DROP TABLE #Temp

可以查看每个表数据量大小。记录下来。

---

SQL SERVER如何获取指定数据库中所有存储过程的参数

 SELECT sp.object_id            AS FunctionId,
       sp.NAME                 AS FunctionName,
       Isnull(param.NAME, '')  AS ParamName,
       Isnull(usrt.NAME, '')   AS [DataType],
       Isnull(baset.NAME, '')  AS [SystemType],
       Cast(CASE
              WHEN baset.NAME IS NULL THEN 0
              WHEN baset.NAME IN ( 'nchar', 'nvarchar' )
                   AND param.max_length <> -1 THEN param.max_length / 2
              ELSE param.max_length
            END AS INT)        AS [Length],
       ''                      AS ParamReamrk,
       Isnull(parameter_id, 0) AS SortId
FROM   sys.objects AS sp
       INNER JOIN sys.schemas b
               ON sp.schema_id = b.schema_id
       LEFT OUTER JOIN sys.all_parameters AS param
                    ON param.object_id = sp.object_id
       LEFT OUTER JOIN sys.types AS usrt
                    ON usrt.user_type_id = param.user_type_id
       LEFT OUTER JOIN sys.types AS baset
                    ON ( baset.user_type_id = param.system_type_id
                         AND baset.user_type_id = baset.system_type_id )
                        OR ( ( baset.system_type_id = param.system_type_id )
                             AND ( baset.user_type_id = param.user_type_id )
                             AND ( baset.is_user_defined = 0 )
                             AND ( baset.is_assembly_type = 1 ) )
       LEFT OUTER JOIN sys.extended_properties E
                    ON sp.object_id = E.major_id
WHERE  sp.type IN ( 'FN', 'IF', 'TF', 'P' )
       AND Isnull(sp.is_ms_shipped, 0) = 0
       AND Isnull(E.NAME, '') <> 'microsoft_database_tools_support'
ORDER  BY sp.NAME,
          param.parameter_id ASC  

如图：

---

SQL判断某列中是否包含中文字符、英文字符、纯数字

一、包含中文字符

select * from 表名 where 列名 like '%[吖-座]%'

二、包含英文字符

select * from 表名 where 列名 like '%[a-z]%' 

三、包含纯数字

select * from 表名 where 列名 like '%[0-9]%'

---

解决 SQLSERVER CTE WITH ')' 附近有语法错误。

WITH t AS (
　　SELECT NTILE(10) OVER (order by id) AS PageNumber,ID FROM App
)

老是提示

')' 附近有语法错误。

确定是SQLSERVER005，网上有说WITH前面加分号;的，有的说开什么分隔符开关的，

整了半天，WITH语句后面是要跟语句的

WITH t AS (
　　SELECT NTILE(10) OVER (order by id) AS PageNumber,ID FROM App
)
SELECT * FROM t

---

数据库索引的填充因子

举个例子：假定你刚刚用缺省的填充因子新创建了一个索引。当sqlserver创建它时，它把索引放在相邻的物理页面上，因为数据能够顺序的读所以这样会有最优的i/o访问。但当表随着、、增加和改变时，发生了页拆分。当页拆分发生时，sqlserver必须在磁盘的某处分配一个新的页，这些新的页和最初的物理页不是连续的。因此，访问使用的是随机的i/o，而不是有顺序的i/o，这样访问索引页会变得更慢。

那么理想的填充因子是多少呢？它依赖于应用程序对sqlserver表的读和写的比率。首要的原则，按照下面的指导：

低更改的表（读写比率为100：1）：100％的填充因子
高更改的表（写超过读）：50-70％的填充因子
读写各一半的：80-90％的填充因子

在为应用程序找到最优的填充因子前也不得不进行试验。不要假定一个低的填充因子总比高的好。低的填充因子会减少页拆分，它也增加了sqlserver查询期间读的页数量，从而减少性能。太低的填充因子不仅增加i/o开销，也影响缓存。当数据页从磁盘移到缓存中时，整个页（包括空的空间）都移到缓存中。所以填充因子越低，不得不移到sqlserver缓存中的页面就越多，意味着同时为其他重要数据页驻留的空间就少，从而降低性能。

如果你没有指定填充因子，缺省的填充因子时0，意味着100％的填充因子（索引的叶页100％的填满，但索引的中间页有预留的空间）。

作为监控的一部分，你要决定新建索引或重建索引时的填充因子是多少。事实上，除了只读数据库，所有的情况，缺省值0都是不适合的。相反，你想要一个填充因子保留合适的自由空间，按照上面的讨论来做。