随笔分类 -  T-SQL Helper Code Snippet

串行化重复列【相同ID】
摘要：--Join the data with the same ID togetherdeclare @tbl table (Sid int identity, ID int, Description varchar(100) )insert into @tblselect 1001, 'Test 1001'union allselect 1001, 'Test 1001 dsdfsdf'union ... 阅读全文
posted @ 2010-10-22 17:23 lp123 阅读(142) 评论(0) 推荐(0)
Scripts to view SQL Server Database Membership
摘要：use mastergo--Get all logins with sysadmin role.select b.name,b.type,b.type_desc,c.namefrom sys.server_role_members ainner join sys.server_principals bon a.member_principal_id = b.principal_idinner jo... 阅读全文
posted @ 2010-07-31 18:48 lp123 阅读(174) 评论(0) 推荐(0)
SQL Server Script to Manage Server LOGIN & Database USER
摘要：-- =======================================================================-- SQL SCRIPT to create LOGIN in the SERVER, and assign sysadmin role to it.-- ===============================================... 阅读全文
posted @ 2010-07-31 18:29 lp123 阅读(218) 评论(0) 推荐(0)
Linked Server related
摘要：http://www.bidn.com/blogs/BradSchacht/ssis/617/server-%E2%80%98servername%E2%80%99-is-not-configured-for-rpc 阅读全文
posted @ 2010-07-15 18:15 lp123 阅读(212) 评论(0) 推荐(0)
Useful scrips for linked server
摘要：IF OBJECT_ID('sp_RmtLogin','P') IS NOT NULL DROP PROC dbo.sp_RmtLoginGO/**************************************************************************** Author: Roy* Date: 04/14/2010* Test: exec sp_RmtL... 阅读全文
posted @ 2010-05-20 18:10 lp123 阅读(239) 评论(0) 推荐(1)
Delete x a not in (select (select...) from x group by)
摘要：[代码] 阅读全文
posted @ 2009-07-08 16:45 lp123 阅读(410) 评论(0) 推荐(0)
修改表中列的数据类型
摘要：alert table test_tb alter column col_nm int_dataType 阅读全文
posted @ 2009-05-12 10:20 lp123 阅读(343) 评论(0) 推荐(0)
SQL Server 2005 Common Built-in Functions
摘要：SQL Server Built-in Functions.-Replace的用法：select replace('zhou','z','x')select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')--20090331151201-Date Format:select CONV... 阅读全文
posted @ 2009-03-30 15:17 lp123 阅读(253) 评论(0) 推荐(0)
SQL SERVER Tricks
摘要：1, How to get the row number of one table:select Row_Number() over ( order by id ), *from testTable2, sp_configure [代码]3, 创建链接服务器 exec sp_addlinkedserver 'Conn_Name', ' ', 'SQLOLEDB', 'server_name OR ... 阅读全文
posted @ 2009-03-26 11:55 lp123 阅读(210) 评论(0) 推荐(0)
Index
摘要：索引类型：1，按存储结构分：聚族索引和非聚族索引；2，按唯一性分：唯一索引和非唯一索引；3，按索引列数分：单列索引和多列索引； 阅读全文
posted @ 2009-03-26 09:36 lp123
Difference between function and store procedure in SQL Server Database.
摘要：The difference between UDF[User Defined Function] and SP[Store Procedure] in SQL Server Database:1, UDF can only have input parameters, however, SP can both have input and output parameters.2, Functio... 阅读全文
posted @ 2009-03-25 16:25 lp123 阅读(401) 评论(0) 推荐(0)