SQLServer - 随笔分类 - princessd8251

Convert and Cast for Date and Money format.

摘要：SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')The below script removes the TAB(Horozontal Tab), Line feed(New line), Carriage Return Charac... 阅读全文

posted @ 2015-01-06 01:15 princessd8251 阅读(312) 评论(0) 推荐(0)

设置SQLServer的行版本控制隔离级别

摘要：1.--查询数据库状态 select name,user_access,user_access_desc,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on from sys.dat... 阅读全文

posted @ 2014-12-27 22:49 princessd8251 阅读(5733) 评论(0) 推荐(2)

SET TEXTSIZE number

摘要：When you using sqlcmd to export some data by a query, you will found some column data is truncated if it is a ntext/text/varch(max)/nvarchar(max).the ... 阅读全文

posted @ 2014-12-24 17:30 princessd8251 阅读(286) 评论(0) 推荐(0)

SQLServer如何处理数据集的维度变化

摘要：Student表有三列,分别是姓名、课程、成绩 Name Curricula Mark 张三语文 70 李四数学 80 王朝英语 59 城南马哲 70 王朝语文 90 我想得到的效果... 阅读全文

posted @ 2014-08-07 23:54 princessd8251 阅读(300) 评论(0) 推荐(0)

DBCC TRACEON/TRACEOFF/TRACESTATUS

摘要：1. enable traceDBCC TRACEON (trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]trace# Is the number of the trace flag to turn on.n Is a placeholder that... 阅读全文

posted @ 2014-07-30 15:43 princessd8251 阅读(692) 评论(0) 推荐(0)

Calculating simple running totals in SQL Server

摘要：Running total for Oracle:SELECT somedate, somevalue,SUM(somevalue) OVER(ORDER BY somedateROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS RunningTo... 阅读全文

posted @ 2014-07-23 18:42 princessd8251 阅读(314) 评论(0) 推荐(0)

Upgrade Image&ntext to varbinarymax&nvarchar(max)

摘要：CREATE PROCEDURE SP_EXEC_WITH_LOG(@I_TICKETNO VARCHAR(10),@I_SQLSTR nvarchar(max))ASBEGIN DECLARE @QUOTATION_TRANSFER_STR nvarchar(max); SET @QUOTATIO... 阅读全文

posted @ 2014-07-23 17:41 princessd8251 阅读(252) 评论(0) 推荐(0)

Infinity loop in cursor iteration

摘要：原始代码如下：begin DECLARE @SQL_STMT NVARCHAR(300),@V_CLIENT_ID INT,@V_PGNAME VARCHAR(1000),@V_LOGID INT;DECLARE C2 CURSOR FOR SELECT PGNAME,CLIENT_ID FROM ... 阅读全文

posted @ 2014-07-16 02:17 princessd8251 阅读(270) 评论(0) 推荐(0)

Tuning SQL via case when statement

摘要：原SQL如下：SQL的主要问题是红色部分居然通过标量查询，反复的查找与SQL相同的基表，很显然这个可以用case when来简化。select a.TRAN_ID,a.AMOUNT,a.BALANCE,a.INVAMT,a.PROMISED,a.INVNO,a.RCLNUM,b.PROBLEM_ID... 阅读全文

posted @ 2014-07-16 02:06 princessd8251 阅读(295) 评论(0) 推荐(0)

SQL Server 2012 新的分页函数 OFFSET & FETCH NEXT

摘要：DECLARE @page INT, @size INT;select @page = 300, @size = 10SELECT *FROM gpcomp1.GPCUSTWHERE company like 'a%'ORDER BY CUSTNOOFFSET (@page -1) * @size ... 阅读全文

posted @ 2014-06-27 11:51 princessd8251 阅读(417) 评论(0) 推荐(0)

SQLServer User and Login Tips

摘要：use masterIF EXISTS (SELECT * FROM sys.databasesWHERE name = 'gpdb83sp')BEGINDROP DATABASE gpdb83spEND;CREATE DATABASE gpdb83spON( NAME = gpdb83sp_dat... 阅读全文

posted @ 2014-06-10 00:49 princessd8251 阅读(586) 评论(0) 推荐(0)

DMV to track the temp file usage for SQLServer

摘要：There are three DMVs you can use to track tempdb usage:sys.dm_db_task_space_usagesys.dm_db_session_space_usagesys.dm_db_file_space_usageThe first two ... 阅读全文

posted @ 2014-06-10 00:39 princessd8251 阅读(280) 评论(0) 推荐(0)

convert Timestamp to Real time

摘要：select r.ring_buffer_address,r.ring_buffer_type,dateadd (ms, r.[timestamp] - sysinfo.sqlserver_start_time_ms_ticks, sysinfo.sqlserver_start_time) as r... 阅读全文

posted @ 2014-06-08 20:48 princessd8251 阅读(487) 评论(0) 推荐(0)

Sequential Read Ahead For SQL Server

摘要：Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize hardware utilization. SQL Server includes two asynch... 阅读全文

posted @ 2014-05-23 01:45 princessd8251 阅读(311) 评论(0) 推荐(0)

SQLServer Note

摘要：1. Grant necessory permission to user account, so it can use SQL profiler.USE masterGRANT ALTER TRACE TO TEST;GRANT VIEW SERVER STATE TO TEST;USE test... 阅读全文

posted @ 2014-05-14 23:56 princessd8251 阅读(288) 评论(0) 推荐(0)

SET Statements for SQLServer

摘要：SET SHOWPLAN_ALL { ON | OFF }It will not execute the TSQL statements.It cannot be specified inside a stored procedure,must be the only statements in a... 阅读全文

posted @ 2014-04-29 21:46 princessd8251 阅读(471) 评论(0) 推荐(0)

Change Tracking of SQLServer

摘要：1.Enable the change tracking at the database level.ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = ON;By Default retention is 2 dyas with auto... 阅读全文

posted @ 2014-04-27 02:04 princessd8251 阅读(686) 评论(0) 推荐(0)

SQLServer DMV Query

摘要：1.DMV Query to identify all active SQL Server SessionsThe query below identifies all currently active SQL Server user connections by their SQL Server ... 阅读全文

posted @ 2014-04-22 19:04 princessd8251 阅读(299) 评论(0) 推荐(0)

SQL Server Logical/Physical Reads

摘要：Summary Info:Logical Reads : Reading Data pages from CachePhysical Reads : Reading Data pages from Hard DiskBuffer Cach Hit Ratio: (logical reads – ... 阅读全文

posted @ 2014-04-22 18:27 princessd8251 阅读(782) 评论(0) 推荐(0)

SET Statements (Transact-SQL)

摘要：The Transact-SQL programming language provides several SET statements that change the current session handling of specific information. The SET statem... 阅读全文

posted @ 2014-04-18 16:28 princessd8251 阅读(307) 评论(0) 推荐(0)

随笔分类 - SQLServer