标签： sqlserver

SQL SERVER查询占用CPU高的SQL语句：

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

SQL SERVER按字母数字的组合排序

SELECT Price, Contact,Number,  GameType, ServerName, Account,Password, Rank, RentDate,  Legends, Detail, IsRent, Id
FROM dbo.AccountInfo
ORDER BY UPPER(LEFT(Number, patindex('%[^a-z]%',Number)-1)) ASC, Convert(int, RIGHT(Number,LEN(Number)- patindex('%[^a-z]%',Number)+1)) ASC, Number ASC

Mysql版本

SELECT  Number
FROM    accountinfo
ORDER   BY         
        CASE WHEN Number REGEXP '^[A-Z]{2}'
            THEN 1
            ELSE 0
        END ASC,
        CASE WHEN Number REGEXP '^[A-Z]{2}'
            THEN LEFT(Number, 2)
            ELSE LEFT(Number, 1)
        END ASC,
        CASE WHEN Number REGEXP '^[A-Z]{2}'
            THEN CAST(RIGHT(Number, LENGTH(Number) - 2) AS SIGNED)
            ELSE CAST(RIGHT(Number, LENGTH(Number) - 1) AS SIGNED)
        END ASC

postgresql版本

SELECT *, (regexp_matches(Number, '[A-Z]+' ))[1], (regexp_matches(Number, '[0-9]+' ))[1] from aaa
    ORDER BY (regexp_matches(Number, '[A-Z]+' ))[1] ASC ,
                  to_number((regexp_matches(Number, '[0-9]+' ))[1], '999999') ASC

查看SQL Server实际占用的物理内存

SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value * 8192.0 ) / 1048576, 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    object_Name LIKE '%Buffer Manager%'
    AND RTRIM(counter_name) IN ( 'Free pages', 'Total pages',
                                 'Database pages' )
UNION SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value / 1024 ), 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    counter_name IN ( 'Target Server Memory (KB)',
                      'Total Server Memory (KB)' )

SQL SERVER标量函数中UPDATE报错。

错误：在函数内对带副作用的运算符 ‘UPDATE CURSOR’ 的使用无效。

解决办法：改为存储过程就可以了。原因暂时没深究，问题解决了就好。

SQL SERVER函数中使用SELECT 给变量赋值，注意函数中不能用SELECT INTO

SELECT @UserId= UserId FROM Tweet WHERE Id= @ReplyTo;

实例代码如下：

CREATE FUNCTION MyFun
(

)
RETURNS int
AS
BEGIN
        DECLARE @ReplyTo int
        DECLARE @UserId int
        DECLARE My_Cursor CURSOR --定义游标
        FOR (SELECT ReplyTo FROM Tweet WHERE ReplyTo<> 0) --查出需要的集合放到游标中
        OPEN My_Cursor; --打开游标
        FETCH NEXT FROM My_Cursor INTO @ReplyTo ; --读取第一行数据
        WHILE @@FETCH_STATUS = 0
        BEGIN                     
      SELECT @UserId= UserId FROM Tweet WHERE Id= @ReplyTo;
             FETCH NEXT FROM My_Cursor INTO @ReplyTo; --读取下一行数据
        END
        CLOSE My_Cursor; --关闭游标
        DEALLOCATE My_Cursor; --释放游标

        RETURN 0

END
GO

SQL SERVER过滤重复查询，在Browser表中过滤重复的IP：

select * from Browser a where not exists (select 1 from Browser where Ip = a .Ip and id < a.id )

SQL SERVER更改字段名

--把Account表的Pwd字段改名为Pwd2
exec sp_rename 'Account.[Pwd]' ,'Pwd2'

SQL SERVER 生成数据库表对应的实体类的存储过程

CREATE PROCEDURE [dbo].[CreateModel]   
    @tableName varchar(100 )
AS
BEGIN
SET NOCOUNT ON;

-- Subquery to return only the copy paste text
Select PropertyColumn from (
    SELECT 1 as rowNr, 'public class ' + @tableName + ' {' as PropertyColumn
    UNION
    SELECT 2 as rowNr, 'public ' + a1. NewType + ' ' + a1.COLUMN_NAME + ' {get;set;}' as PropertyColumn
    -- ,* comment added so that i get copy pasteable output
     FROM
    (
        /*using top because i'm putting an order by ordinal_position on it.
        putting a top on it is the only way for a subquery to be ordered*/
        SELECT TOP 100 PERCENT
        COLUMN_NAME ,
        DATA_TYPE ,
        IS_NULLABLE ,
        CASE
            WHEN DATA_TYPE = 'varchar' THEN 'string'
            WHEN DATA_TYPE = 'nvarchar' THEN 'string'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
            WHEN DATA_TYPE = 'char' THEN 'string'                       
            WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
            WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
            WHEN DATA_TYPE = 'xml' THEN 'string'
        END AS NewType
        FROM INFORMATION_SCHEMA .COLUMNS
        WHERE TABLE_NAME = @tableName
        ORDER BY ORDINAL_POSITION
        ) AS a1
    UNION
    SELECT 3 as rowNr,   '} // class ' + @tableName
    ) as t Order By rowNr asc
END

--调用下面的代码生对指定表的实体类
Helper_CreatePocoFromTableName 'Account'

--调用下面的代码生成所有实体实
USE Linshu
GO
    DECLARE @field1 nvarchar(400 )
    DECLARE cur CURSOR LOCAL for

    SELECT TABLE_NAME FROM information_schema .tables
    OPEN cur
    FETCH NEXT FROM cur INTO @field1 --, @field2
    WHILE @@FETCH_STATUS = 0 BEGIN         
        exec Helper_CreatePocoFromTableName @field1 -- , @field2           
        fetch next from cur into @field1 -- , @field2
    END

close cur
deallocate cur

在SQL SERVER的 SELECT语句中执行类型if的条件选择，使用CASE … WHEN…语句，如下：

SELECT
t2.UserID , --操作人
t2.OrderNum , --订单编号
t2.CustomerID , --客户姓名
t2.StyleNo , --款号
t2.Size , --尺码
t2.Picture , --产品图片
t2.Number , --数量


 --工厂
(SELECT Name FROM [User] t3 WHERE t3. UserID=t1 .UserID) AS Factory,
t1.OrderType ,

-- 采购方式
PurchaseWay=
CASE t1 .Type
        WHEN 1 THEN '现付'
        WHEN 2 THEN '银行转帐'
        WHEN 3 THEN '担保交易'
        WHEN 4 THEN '结算'
END,

t1.LoginName , -- 采购人
t1.CreateTime , --采购日期
t1.ShouldArrivalDate , --工厂应交付日期
t1.DeliveryDate , --工厂发货日期
t1.ActualArrivalDate , --我方签收日期

--采购单状态
PurchaseStatus=
CASE t1 .Status
        WHEN 1 THEN '未完成'
        WHEN 2 THEN '取消'
        WHEN 3 THEN '完成'
        WHEN 4 THEN '中途变更'
END,

t1.Remark , --采购备注

--款项结算
SettlementStatus=
CASE t1 .StyleSettlement
        WHEN 1 THEN '未结算'
        WHEN 2 THEN '已结算未付款'
        WHEN 3 THEN '已结算已付款'
END,


t1.Amount --采购金额
FROM Purchasing t1, [Order] t2
WHERE t1 .OrderID = t2 .OrderID