SQL
文章平均质量分 53
incognito007
这个作者很懒,什么都没留下…
展开
-
SQL SERVER - EXTENDED EVENT 监视错误和存储过程
监视错误CREATE EVENT SESSION [error_trap] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.cl原创 2013-03-22 22:00:47 · 1161 阅读 · 0 评论 -
T-SQL XQUERY.QUERY
SELECTarthurname,region,CONVERT(NVARCHAR(max),BooksXML.query('for $s in /books/booklet $n := data($s/@name)let $p := concat($n,"-")return $p'))AS BooksListFROM [XmlsampleDB].[dbo].[Arthur]a原创 2012-08-26 06:19:56 · 941 阅读 · 0 评论 -
T-SQL HIERACHYID.GETREPARENTEDVALUE
SELECT NodeId, NodeId.ToString() AS NodeIdPath, dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPathFROM familyORDER BY NodeLevel, NodeId NodeId NodeIdPath NodeIdDisplayPath0x / 爷爷0x58原创 2012-09-01 09:21:05 · 939 阅读 · 0 评论 -
T-SQL HIERACHID.ISDESCENDANTOF
DECLARE @ParentNodeId hierarchyidSELECT @ParentNodeId = NodeId FROM family WHERE familyId = 113SELECT NodeId.ToString() AS NodeIdPath, nodeid,dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPa原创 2012-09-01 06:31:12 · 1045 阅读 · 0 评论 -
T-SQL HIERACHYID.GETROOT
SELECT NodeId.ToString() AS NodeIdPath, dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPath,* FROM FAMILY WHERE NodeId = hierarchyid::GetRoot()GONodeIdPath NodeIdDisplayPath NodeId NodeLevel F原创 2012-09-01 06:43:47 · 764 阅读 · 0 评论 -
T-SQL HIERACHYID.GETANCESTOR
SELECT NodeId.ToString() AS NodeIdPath, dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPath,* FROM FAMILY WHERE NodeId.GetAncestor(2) = (SELECT NodeId FROM FAMILY WHERE FAMILYId = 1原创 2012-09-01 06:40:07 · 858 阅读 · 0 评论 -
Hierachical Indexing
You can create a depth-first index or a breadth-first index (or both) on your hierarchical tables. The two types differ in how SQL Server physically stores node references in the index. Defining depth原创 2012-09-02 01:25:56 · 769 阅读 · 0 评论 -
T-SQL 替换多个空格
DECLARE @Demo TABLE(OriginalString NVARCHAR(4000)) INSERT INTO @Demo (OriginalString) SELECT ' I am Incognito, What just for a test!' UNION ALL SELECT '看看 效果原创 2012-08-21 04:17:35 · 1401 阅读 · 0 评论 -
T-SQL 计算固定资产折旧(双倍余额法)
同样应用 CTE,计算固定资产折旧(双倍余额法) DECLARE @Assets TABLE (NAME VARCHAR(20), PurchaseCost MONEY, Period INT)DECLARE @DBFactor INTSET @DBFactor = 2 -- 双倍INSERT INTO @Assets SELECT '计算机',原创 2012-07-21 20:20:05 · 1487 阅读 · 0 评论 -
T-SQL 计算固定资产折旧(直线法)
使用 CTE, 计算固定资产折旧(直线法)。 DECLARE @Assets TABLE (NAME VARCHAR(20), PurchaseCost MONEY, Period INT)INSERT INTO @AssetsSELECT '计算机', 5000, 24;WITH SLDepSched (AssetID, [Month], Period -- 固定资产 ,原创 2012-07-21 20:11:29 · 1513 阅读 · 0 评论 -
T-SQL 数字位位数求和
数字 123456, 求和 =1+2+3+4+5+6 =21 declare @i intset @i=123456select sum(substring(ltrim(@i),number,1)*1)from master..spt_values where type='p' and number between 1 and len(@i)原创 2012-08-03 23:30:58 · 1152 阅读 · 1 评论 -
T-SQL 模糊和精确查找
以下语句类似查找中的 LIKE , EXACT. -- Select Search criteria, If not WILDCARDMATCH, will search for Exact Match DECLARE @SEARCHCRETERIA NVARCHAR(1000) = 'WILDCARDMATCH'-- Enter Searching value DECLARE @S原创 2012-07-14 20:46:12 · 957 阅读 · 0 评论 -
WQL - EVENT QUERY
WMI EVENT QUERY EVENT-WQL = “SELECT” “FROM” / OPTIONAL-WITHIN = ["WITHIN" ]INTERVAL = 1*DIGITEVENT-WHERE = ["WHERE" ]EVENT-EXPR = ( ( “ISA” ) / )["GROUP WITHIN" ( ["BY" [ DOT] ]原创 2012-07-13 09:20:12 · 789 阅读 · 0 评论 -
SQL SERVER 2012 - MEMORY MANAGEMENT
1,The following table indicates whether a specific type of memory allocation is controlled by themax server memory (MB) andmin server memory (MB) configuration options.Collapse this tableExpan原创 2012-06-24 09:07:10 · 1192 阅读 · 0 评论 -
T-SQL MERGE AND OUTPUT
1,CREATE TABLE #t(id int IDENTITY(1,1) PRIMARY KEY,val int NOT NULL,testguid uniqueidentifier NOT NULL DEFAULT NEWID());GOINSERT #t (val)OUTPUT INSERTED.id, INSERTED.val, INSERTED.原创 2012-06-23 02:22:59 · 965 阅读 · 0 评论 -
T-SQL检查列校验
下面的代码检查列校验不匹配库校验。 IF OBJECT_ID('tempdb..#res')IS NOT NULL DROP TABLE #resGODECLARE@db sysname,@sql nvarchar(2000)CREATE TABLE #res(server_namesysname, db_namesysname, db_collat原创 2012-06-09 03:21:06 · 900 阅读 · 0 评论 -
POWERSHELL-在不同的实例和数据库执行SQL
有时,要执行SQL在不同的实例和数据库,下面的POWERSHELL,很方便。 $instances = @( @( 'Server1', 'someDatabase' ), @( 'Server2', 'AnotherDatabase' ), @( 'Server32', 'FooDB' ) # 添加)$deployScript = Get-Conte原创 2012-05-06 08:39:08 · 864 阅读 · 0 评论 -
SQL SERVER 2012 T-SQL 新增特性- CONCAT
SQL SERVER 2012 T-SQL 新增特性- CONCAT函数,做了很大改进,比以前简单好用。declare @data as table ( row_id tinyint identity(1,1) primary key, order_dt datetime2(2))insert into @data(order_dt)values ('20110305 4:30PM'),原创 2012-04-19 23:56:26 · 930 阅读 · 0 评论 -
T-SQL XQUERY.VARIABLE
DECLARE @bn NVARCHAR(max) = '剑'SELECT ArthurName,Region,CONVERT(NVARCHAR(max),BooksXML.query('for $s in /books/booklet $n := data($s/@name)let $p := concat($n,"-")where $s/@name[contains(.,sql原创 2012-08-26 07:39:37 · 964 阅读 · 0 评论 -
T-SQL XQUERY.NODES
SELECTarthurname,region,n.c.query('.') AS BookNameFROM [XmlsampleDB].[dbo].[Arthur]CROSS APPLYBooksXML.nodes('/books/book') AS n(c)arthurname region BookName金庸 香港 金庸 香港 金庸 香港 金庸 香港 金原创 2012-08-26 09:02:17 · 1112 阅读 · 0 评论 -
T-SQL XQUERY.EXIST
SELECT*FROM [XmlsampleDB].[dbo].[Arthur]WHEREBooksXML.exist('books/book/@name[contains(.,"侠")]')=1 ANDregion ='香港arthurId ArthurName Region BooksXML3 金庸 香港 <book name="飞狐外传" /><book name="鸳原创 2012-08-26 06:14:48 · 1820 阅读 · 0 评论 -
Window Server 2012 -安装备忘
1,下载安装 WINDOW SERVER 2012 EVALUATION VERSION; 2,输入 DISM /ONLINE /SET-EDITION:SERVERDATACENTER /PRODUCTKEY: (有效的产品密斯)/ACCEPTEULA; 3,激活 WINDOW SERVER 2012; 4,ADMINISTRATIVE TOOL -> LOCALE SE原创 2013-03-13 07:45:50 · 1617 阅读 · 0 评论 -
T-SQL 一次性查询计划优化
如果内存不足,检查一次性查询计划缓存:SELECT SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'FROM sys.dm_exec_cached_plans;--总缓存 SELECT objtype AS 'Type',COUNT(*) AS '# Plans',SUM(CAST(size_in原创 2012-12-23 01:13:27 · 893 阅读 · 0 评论 -
T-SQL 隐性模式下显性事务
USE AdventureWorks2012 GO SET IMPLICIT_TRANSACTIONS ON GO PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON' GO SELECT 'Tran count outside transaction'= @@TRANCOUNT --@@TRANC原创 2012-12-14 02:45:07 · 1806 阅读 · 0 评论 -
T-SQL 列表达式排序
首先,看以下代码SELECT T.姓名,T.班级,SUM(T.得分) AS 总得分FROM (SELECT 姓名,班级,科目,得分FROM [成绩表$]UNION ALLSELECT 班级&"小计" AS 姓名,班级,科目,AVG(得分)FROM [成绩表$]GROUP BY 科目,班级UNION ALLSELECT "合计" AS 姓名,null AS 班级,科目,AVG(得原创 2012-12-14 04:44:48 · 2251 阅读 · 0 评论 -
T-SQL 查询优化之链接和平行
SQL SERVER 有三种链接逻辑操作符,下面简单说一下: 1,NESTED LOOP JOIN它的算法,外面的输入执行一次,而里边的输入对于外面的每一行需要执行一次。一般来说,但外面的表比较小,里边的表比较大,链接键上有索引,查询优化器会选择它,而且效率高。 2,MERGE JOIN对于这种算法,两个输入中到大表,链接是相等并且排序,优化器会采用。相对于NEST原创 2012-12-24 07:02:39 · 962 阅读 · 0 评论 -
T-SQL 查询优化之聚合
SQL SERVER 两种聚合, STREAM AGGREGATE,HASH AGGREGATE.哈希和排序都需要内存,哈希小表要内存,排序如果内存不足,需要临时数据库。 1,STREAM AGGREGATE返回单一数值,没有 GROUP BY,总会使用它;如果有 GROUP BY,排序使用排序操作符或索引。 2,HASH AGGREGATE一般应用于比较大的表,它原创 2012-12-24 05:43:31 · 859 阅读 · 0 评论 -
T-SQL 查询优化之扫描搜寻
扫描有,表,聚集索引,非聚集索引;搜寻有,聚集索引,非聚集索引搜寻。显然,如果查询只是聚集和非聚集索引包含的列,没有多少讨论意义。 下面的问题自然是,如果查询的列不在聚集和非聚集索引包含之内,优化查询器如何决定? 还是看个例子:USE AdventureWorksGOSELECT AddressID, City, StateProvinceID, ModifiedDat原创 2012-12-23 12:34:45 · 796 阅读 · 0 评论 -
T-SQL 查询优化之联接顺序
联接顺序,是查询优化的最复杂问题之一,从七十年代以来,一直是广泛探索的主题。随着联接表的增加,搜索空间的扩大,必然导致计划数量的增大。联接一次只能包括两个表,因此,N个表联接有 N-1次联接。当然,下一个联接不需要等到上一个联接完成。两个属性:1,交换A JOIN B = B JOIN A它确定那个表作为第一个表,例如,在 NESTED LOOP JOIN 中,第一个表是作为外原创 2012-12-23 03:08:54 · 939 阅读 · 0 评论 -
T-SQL 看看钓鱼岛离到底我们有多远
根据国家海洋局2012年9月10日公布的钓鱼岛和赤尾屿坐标,看看钓鱼岛到底离我们有多远?DECLARE @a geography= (SELECT geom FROM dbo.CHN_adm1 WHERE NAME_1='diaoyudao')SELECT name_2,'->钓鱼岛' AS name,geom.STDistance(@a)/1000 AS diatance FROM原创 2012-11-28 03:56:49 · 5737 阅读 · 0 评论 -
XML查询示例五
使用XML,合并和拆分 CREATE TABLE #tempCityState (State NVARCHAR(10), City NVARCHAR(50))INSERT INTO #tempCityStateSELECT '江苏', '南京' UNIONSELECT '江苏', '苏州' UNIONSELECT '江苏', '无锡' UNIONSELECT '江苏', '常州原创 2012-11-07 03:07:59 · 909 阅读 · 0 评论 -
XML查询示例二
1,提取XML列,使用VALUE;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)SELECT FirstName, MiddleName, LastName, Demographics.value('(/原创 2012-11-06 03:58:44 · 955 阅读 · 0 评论 -
XML查询示例四
分离XMLDECLARE @xml XMLSET @xml = ( SELECT TOP 2 Name, BusinessEntityID AS ID, Demographics.query('/') FROM Sales.Store AS Store WHERE SalesPersonID = 2原创 2012-11-06 04:17:36 · 682 阅读 · 0 评论 -
XML查询示例三
1,组合XML列,使用 FOR XML AUTOSELECT TOP 2 Name, BusinessEntityID AS ID, Demographics.query('/')FROM Sales.Store AS StoreWHERE SalesPersonID = 282FOR XML AUTOGO http://schemas.microsoft.c原创 2012-11-06 04:07:43 · 782 阅读 · 0 评论 -
XML查询示例一
1,创建XML, 使用 FOR XML AUTOUSE AdventureWorks2012SELECT BusinessEntityID, PersonType, Title, FirstName, MiddleName, LastName, SuffixFROM Person.PersonWHERE BusinessEntityID =原创 2012-11-06 03:34:54 · 2985 阅读 · 0 评论 -
T-SQL XQUERY.MODIFY
UPDATE XmlsampleDB.dbo.Arthur SET BooksXML.modify(' insert into /books[1]') WHERE region = '台北'DECLARE @bn NVARCHAR(max) = '剑'SELECT ArthurName,Region,CONVERT(NVARCHAR(max),Books原创 2012-08-28 05:04:41 · 1576 阅读 · 1 评论 -
Xml Indexing
XML indexing consists of two categories: primary and secondary indexes. These two indexes types provide an indexing relationship within the XML documents similar to the relationship between clustered原创 2012-08-28 06:19:34 · 614 阅读 · 0 评论 -
T-SQL XQUERY.VALUE
SELECTarthurname,region,BooksXML.value('/books[1]/@arthurBio','nvarchar(max)') AS ArthurBio,BooksXML.value('count(books/book)','int') AS BooksCountFROM [XmlsampleDB].[dbo].[Arthur]ORDER BYBooks原创 2012-08-26 06:16:55 · 2155 阅读 · 0 评论 -
SQL SERVER 2012 T-SQL 新增特性-TRY_CONVERT
SQL SERVER 2012 T-SQL 新增函数-TRY_CONVERT,非常耗用。看一个例子:declare @numerics as table ( row_id tinyint identity(1,1) primary key, numeric_val varchar(100))insert into @numerics(numeric_val)values ('35.原创 2012-04-19 23:48:02 · 1438 阅读 · 0 评论 -
SQL SERVER 2012 T-SQL 发现存储过程元数据
SQL SERVER 2012 T-SQL,新增了一个DMV,可以发现存储过程元数据,sys.dm_exec_describe_first_result_set_for_object。 USE AdventureWorks2008R2;DECLARE @ObjectID INT;SET @ObjectID = OBJECT_ID('AdventureWorks2008R2.dbo.原创 2012-04-19 23:28:03 · 1013 阅读 · 0 评论