标签 SQL 下的文章

sql日期格式化

December 28, 2016 
  0   或   100   (*)     默认值   mon   dd   yyyy   hh:miAM（或   PM）     
  1   101   美国   mm/dd/yyyy     
  2   102   ANSI   yy.mm.dd     
  3   103   英国/法国   dd/mm/yy     
  4   104   德国   dd.mm.yy     
  5   105   意大利   dd-mm-yy     
  6   106   -   dd   mon   yy     
  7   107   -   mon   dd,   yy     
  8   108   -   hh:mm:ss     
  -   9   或   109   (*)     默认值   +   毫秒   mon   dd   yyyy   hh:mi:ss:mmmAM（或   PM）     
  10   110   美国   mm-dd-yy     
  11   111   日本   yy/mm/dd     
  12   112   ISO   yymmdd     
  -   13   或   113   (*)     欧洲默认值   +   毫秒   dd   mon   yyyy   hh:mm:ss:mmm(24h)     
  14   114   -   hh:mi:ss:mmm(24h)     
  -   20   或   120   (*)     ODBC   规范   yyyy-mm-dd   hh:mm:ss[.fff]     
  -   21   或   121   (*)     ODBC   规范（带毫秒）   yyyy-mm-dd   hh:mm:ss[.fff]     
  -   126(***)   ISO8601   yyyy-mm-dd   Thh:mm:ss:mmm（不含空格）     
  -   130*   科威特   dd   mon   yyyy   hh:mi:ss:mmmAM     
  -   131*   科威特   dd/mm/yy   hh:mi:ss:mmmAM

Sql Server 中一个非常强大的日期格式化函数

Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

常用：

Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16

SQL中CONVERT转化函数的用法
CONVERT的使用方法:
////////////////////////////////////////////////////////////////////////////////////////
格式:

CONVERT(data_type,e­xpression[,style])

说明:
此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)
相互转换的时候才用到.

例子:

Select CONVERT(varchar(30),getdate(),101) now

结果为

 now
 ---------------------------------------
 09/15/2001

/////////////////////////////////////////////////////////////////////////////////////

style数字在转换时间时的含义如下

Style(2位表示年份)Style(4位表示年份)输入输出格式
-0 or 100mon dd yyyy hh:miAM(或PM)
1101mm/dd/yy
2102yy-mm-dd
3103dd/mm/yy
4104dd-mm-yy
5105dd-mm-yy
6106dd mon yy
7107mon dd,yy
8108hh:mm:ss
-9 or 109mon dd yyyy hh:mi:ss:mmmmAM(或PM)
10110mm-dd-yy
11111yy/mm/dd
12112yymmdd
-13 or 113dd mon yyyy hh:mi:ss:mmm(24小时制)
14114hh:mi:ss:mmm(24小时制)
-20 or 120yyyy-mm-dd hh:mi:ss(24小时制)
-21 or 121yyyy-mm-dd hh:mi:ss:mmm(24小时制)

原文：http://www.cnblogs.com/hantianwei/archive/2009/12/03/1616148.html

几段排查SQL Server占用CPU过高的SQL

November 14, 2016

1.查看当前的数据库用户连接有多少

 USE master
 GO
 --如果要指定数据库就把注释去掉
 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
 SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

2.查看各项指标是否正常，是否有阻塞，选取了前10个最耗CPU时间的会话

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'  
ORDER BY [cpu_time] DESC

3.查看具体的SQL语句，需要在SSMS里选择以文本格式显示结果

--在SSMS里选择以文本格式显示结果
SELECT TOP 10 
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50  
ORDER BY [cpu_time] DESC

4.查看CPU数和user scheduler数和最大工作线程数，检查worker是否用完也可以排查CPU占用情况

 --查看CPU数和user scheduler数目
 SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
 --查看最大工作线程数
 SELECT max_workers_count FROM sys.dm_os_sys_info

5.查看worker是否用完，如果达到最大线程数的时候需要检查blocking

SELECT
scheduler_address,
scheduler_id,
cpu_id,
status,
current_tasks_count,
current_workers_count,active_workers_count
FROM sys.dm_os_schedulers

对照表：
各种CPU和SQLSERVER版本组合自动配置的最大工作线程数

CPU数32位计算机64位计算机
<=4256512
8288576
16352704
32480960

6.查看会话中有多少个worker在等待

 SELECT TOP 10
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句', 
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows 
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

7.查看ASYNC_NETWORK_IO等待

（注：比如我当前执行了查询SalesOrderDetail_test表100次，由于表数据非常多，所以SSMS需要把SQLSERVER执行的结果慢慢的取走，造成了ASYNC_NETWORK_IO等待）

 USE [AdventureWorks]
 GO
 SELECT * FROM dbo.[SalesOrderDetail_test]
 GO 100

8.查询CPU占用高的语句

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

9.查询缺失索引

SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

修改SQL数据库表架构名

October 24, 2016

有时候使用SQL小号登录的数据库建的表，一不小心建的表就是当前登录的这账号的架构了，对之后的使用会造成一定的麻烦，修改表的架构名，可以使用以下两种方法：
一、通过SQL Management Studio来修改
要修改的表右键，设计，之后F4打开属性，在属性中的架构里选择要修改成的架构，比如dbo

二、通过下面的SQL语句：

ALTER SCHEMA 新架构 TRANSFER 旧架构.对象名称

如

ALTER SCHEMA dbo TRANSFER news.newslist

把newslist表从原来架构news改成dbo

以上

清除数据库日志文件

March 7, 2016

随着使用，数据库日志会越来越大，直接影响数据库的性能，这时候就需要对数据库的日志进行瘦身了，下面的脚本可以对数据库的日志进行清理（dbname为数据库名，dbname_log为数据库的日志文件名（不带扩展名））：

USE [master]
GO
ALTER DATABASE dbname SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE dbname SET RECOVERY SIMPLE --简单模式
GO

USE whbbs
GO
DBCC SHRINKFILE (N'dbname_Log' , 1, TRUNCATEONLY)

GO
--这里的dbname_Log 如果不知道在sys.database_files里是什么名字的话，可以用以下注释的语句进行查询

--USE dbname
--GO

--SELECT file_id, name FROM sys.database_files;
--GO

USE [master]
GO
ALTER DATABASE dbname SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE dbname SET RECOVERY FULL --还原为完全模式
GO

以上。

恢复没有日志文件的SQL数据库

May 17, 2011

由于种种原因，我们如果当时仅仅备份了mdf文件，那么恢复起来就是一件很麻烦的事情了。
如果您的mdf文件是当前数据库产生的，那么很侥幸，也许你使用sp_attach_db或者sp_attach_single_file_db可以恢复数据库，
但是会出现类似下面的提示信息

设备激活错误。物理文件名 'C:Program FilesMicrosoft SQL ServerMSSQLdatatest_Log.LDF' 可能有误。
已创建名为 'C:Program FilesMicrosoft SQL ServerMSSQLDatatest_log.LDF' 的新日志文件。

但是，如果您的数据库文件是从其他计算机上复制过来的，那么很不幸，也许上述办法就行不通了。你也许会得到类似下面的错误信息

服务器: 消息 1813，级别 16，状态 2，行 1
未能打开新数据库 'test'。Create DATABASE 将终止。
设备激活错误。物理文件名 'd:test_log.LDF' 可能有误。

怎么办呢？别着急，下面我们举例说明恢复办法。
A．我们使用默认方式建立一个供恢复使用的数据库(如test)。可以在SQL Server Enterprise Manager里面建立。
B．停掉数据库服务器。
C．将刚才生成的数据库的日志文件test_log.ldf删除，用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件test_data.mdf。
D．启动数据库服务器。此时会看到数据库test的状态为“置疑”。这时候不能对此数据库进行任何操作。
E．设置数据库允许直接操作系统表。此操作可以在SQL Server Enterprise Manager里面选择数据库服务器，按右键，选择“属性”，在“服务器设置”页面中将“允许对系统目录直接修改”一项选中。也可以使用如下语句来实现。

use master  
go  
sp_configure 'allow updates',1  
go   
reconfigure with override  
go

F．设置test为紧急修复模式

update sysdatabases set status=-32768 where dbid=DB_ID('test')

此时可以在SQL Server Enterprise Manager里面看到该数据库处于“只读置疑脱机紧急模式”可以看到数据库里面的表，但是仅仅有系统表

G．下面执行真正的恢复操作，重建数据库日志文件

dbcc rebuild_log('test','C:Program FilesMicrosoft SQL ServerMSSQLDatatest_log.ldf')

执行过程中，如果遇到下列提示信息：

服务器: 消息 5030，级别 16，状态 1，行 1
未能排它地锁定数据库以执行该操作。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

说明您的其他程序正在使用该数据库，如果刚才您在F步骤中使用SQL Server Enterprise Manager打开了test库的系统表，那么退出SQL Server Enterprise Manager就可以了。
正确执行完成的提示应该类似于：
警告: 数据库 'test' 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致性。将必须重置数据库选项，并且可能需要删除多余的日志文件。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
此时打开在SQL Server Enterprise Manager里面会看到数据库的状态为“只供DBO使用”。此时可以访问数据库里面的用户表了。

H．验证数据库一致性（可省略）

dbcc checkdb('test')

一般执行结果如下：

CHECKDB 发现了 0 个分配错误和 0 个一致性错误（在数据库 'test' 中）。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

I．设置数据库为正常状态

sp_dboption 'test','dbo use only','false'

如果没有出错，那么恭喜，现在就可以正常的使用恢复后的数据库啦。

J．最后一步，我们要将步骤E中设置的“允许对系统目录直接修改”一项恢复。因为平时直接操作系统表是一件比较危险的事情。当然，我们可以在SQL Server Enterprise Manager里面恢复，也可以使用如下语句完成

sp_configure 'allow updates',0  
go   
reconfigure with override  
go

分类

最新文章

最近回复

归档

标签云

C# .net core asp.net 情感 SQL mongodb sql server EasyUI linux 身份验证 typecho .net sql注入 安全 kms 激活 MVC https IIS 高并发 IE 坑爹 服务器 mysql Combobox Datagrid 歌 口语 数据抓取 远程下载

其它