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

た入场券 2021-06-26 16:06 560阅读 0赞

--------------------

## `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

如图：  
![在这里插入图片描述][2019052917533294.png]

--------------------

## 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`都是不适合的。相反，你想要一个填充因子保留合适的自由空间，按照上面的讨论来做。

[2019052917533294.png]: /images/20210626/6734c52f00b5427bb40ae40ca194319c.png