分类目录归档：SQL Server

SQL SERVER 2008 R2 sequence

发表评论 
-- Create the sequencing table that is used to hold the sequence values.

CREATE TABLE SQL_SERVER_SEQUENCE (
NAME VARCHAR(30) NOT NULL,
VALUE BIGINT DEFAULT 0 NOT NULL,
CONSTRAINT PK_SQL_SERVER_SEQUENCE PRIMARY KEY (NAME)
);
-- Create the stored procedure that is responsible for incrementing the sequence and return it.
ALTER PROCEDURE SQL_SERVER_SEQUENCE_NEXTVAL
@name VARCHAR(30)
AS
BEGIN
DECLARE @value BIGINT
BEGIN TRANSACTION
UPDATE SQL_SERVER_SEQUENCE
SET @value = value = value + 1
WHERE name = @name;
COMMIT TRANSACTION
SELECT @value AS NEXTVAL
END;

创建后，在表SQL_SERVER_SEQUENCE中添加一行

如：NAME    VALUE

         yypz        0

用法：

SQL_SERVER_SEQUENCE_NEXTVAL 'yypz'

sql server游标

发表评论 
declare test_cursor cursor for
select  t1.baijiahao as bjh_name, t1.baijiahao_home_url as home_url, t2.category_name as domain from baijia_articles t1
inner join category t2
on t1.cat_id = t2.id
open test_cursor
declare @bjh_name nvarchar(50)
declare @home_url nvarchar(90)
declare @domain nvarchar(50)
fetch next from test_cursor into @bjh_name, @home_url, @domain
while @@fetch_status=0
begin
       
       if not exists　( select id from baijiahao where bjh_name=@bjh_name)  insert into baijiahao(bjh_name, home_url, domain) values(@bjh_name, @home_url, @domain)
       fetch next from test_cursor into @bjh_name, @home_url, @domain
end
close test_cursor
deallocate test_cursor

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函数中使用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