Trending Up...

declare @d datetimeset @d = 2009-05-05select dateadd(dd, -day(@d)+1, @d) as curr_mth_start, dateadd(dd, -day(@d), @d) as last_mth_end

ISOLATION 是Connection的一个property，每个session都有自己的Isolation Level1）Read Uncommitted (不做控制)能读别人已修改未提交的databegin traninsert(can read data)2)Read committed不能读别人已修改未提交的databegin traninsert直到commit(can read d

Locking in Microsoft SQL ServerAlexander Chigrikchigrik@mssqlcity.comIntroductionTransaction Isolation LevelsLock typesLocking optimizer hintsDeadlocksView locks (sp_lock)LiteratureIntroductionIn this

Functional Dependencies (FDs) 1->1A->B: If I know your vendor code, I can determine the vendor nameMultiValued Dependency (MVD) 1 -> *A->->B: If I know the customer code, I can determine the list of t

@2007 June 02MS BI Studiofact table (transaction table)dim table (master file: product, customer)BI Studio1)Datasource: Delphi ADOConnection2)Datasource View: ADOTable3)Cube4)Dimensionmainly involve:

-- find the minimal cost routecreate table flights(departure char(20), destination char(20), cost int)insert flights select Chicago, New York, 10 unionselect Chicago, Milwaukee, 20 unionselect

-- Subset Construction: convert NFA to DFA-- require: NFA_trans_table is filled with NFA state data-- ========================================================================exec clearobj DFA_states

declare @personnel table(emp varchar(30), lft int, rgt int)insert @personnelselect Albert, 1, 12union all select Bert, 2, 3 union all select Chuck, 4, 11 union all select Donna, 5, 6 union all

/*then materialized path representation of tree is easy to output: order by path, level*/declare @orgchart table (emp varchar(30), path varchar(100))insert @orgchartselect Albert, 1union all selec

if object_id(clearobj) is not null drop proc clearobjgocreate proc clearobj @obj nvarchar(50), @debug int = 0with encryptionas    declare @sql nvarchar(256)    select @sql = drop +        case (s

-- ========================================================================exec clearobj DFA_simulate, 1gocreate proc DFA_simulate    @str nvarchar(100), @match bit outputwith encryptionasdeclare @i

-- Thompson Construction : convert Regular Expression Parse Tree to NFA-- require: parse_node table is filled with tree-struct data-- ==================================================================

1 NF=====================================================================Acolumn is atomic, not array, no repeating semantic columnsanti-example:Customers(CustID INT, CustName VARCHAR(30)...)SQL 2005

-- convert regular expression string to parse treeif object_id(clearobj) is not null drop proc clearobjgocreate proc clearobj @obj nvarchar(50), @debug int = 0with encryptionas    declare @sql nvarc

declare @tree table(emp varchar(30), boss varchar(30))insert @treeselect Albert, NULLunion all select Bert, Albertunion all select Chuck, Albertunion all select Donna, Chuckunion all sel

USE tempdbgoIF object_id([tb]) IS NOT NULL    DROP TABLE [tb]goCREATE TABLE [tb](    [id] int,    [amt] int)INSERT [tb]SELECT 1,       10UNION ALLSELECT 2,       -8UNION ALLSELECT 3,       7UNION AL

use tempdbgoselect *into #tempfrom (select apple as product, 2008-10-02 as saledate, 87 as qtyunion all select apple, 2008-10-03, 34union all select apple, 2008-11-17, 75union all select

 declare @str varchar(100)set @str = C:/data/test.csvdeclare @idx int, @len intset @len = len(@str)set @idx = @lenwhile(@idx > 0 and substring(@str, @idx, 1) if @idx - 1 > 0    print path = + sub

 declare @var intset @var = 1print @var = + isnull(str(@var), null)select top 1 @var = [object_id] from sys.objects where 1=2-- @var != nullprint @var = + isnull(str(@var), null)

use CheckSum function to turn a string key to a hash key(integer), this will improve performance

Extract from : SQL Server Error Handling Workbenchhttp://www.simple-talk.com/sql/t-sql-programming/sql-server-error-handling-workbench/--Catch Error:--=============================================BEGI

Select * from test where convert(varbinary, column1) = convert(varbinary, ABCD)  

if object_id(proc_print_tree) is not null    drop procedure proc_print_treegocreate procedure proc_print_tree @raw_data_sql nvarchar(1000), @debug bit = 0with encryptionas    if object_id(tempdb..#

-- Credit to JOE CELKO-- http://archives.postgresql.org/pgsql-sql/2001-11/msg00004.php--  CREATE TABLE Personnel --  (emp CHAR(10) NOT NULL PRIMARY KEY, --   lft INTEGER NOT NULL UNIQUE CHECK (lft > 0

select CustID, CustName from openrowset(Microsoft.Jet.OLEDB.4.0, C:/trade.mdb;Admin;, Customer)

/*The organizational chart would look like this as a directed graph:            Albert (1,12)            /        /          /            /    Bert (2,3)    Chuck (4,11)                   /    |   /  

 IF OBJECT_ID(sp_ThrowExp) IS NOT NULL    DROP PROC sp_ThrowExpGOCREATE PROC sp_ThrowExpAS    RAISERROR(Error occurs., 16, 1)GOIF OBJECT_ID(sp_OuterProc) IS NOT NULL    DROP PROC sp_OuterProcGOC

SELECT DATEADD(DAY,0, DATEDIFF(DAY,0, GETDATE()))SELECT  CAST(CAST(GETDATE() AS INT)  AS DATETIME)

/*    search a string in all databaes tables and all fields*/if object_id(search_db) is not null    drop proc search_dbgocreate proc search_db    @table nvarchar(100),    @cond nvarchar(512)asdeclar

use pubsgodeclare @table_name varchar(35), -- if the table have too much fields ,this proc may be malfunction @column_list nvarchar(4000) -- field list, separated with ,select @table_name

:: install all .sql file into local SQL server:: database=pubs, user=sa, password=echo offfor %%f in (*.sql) do (echo %%f & osql /n /S. /Usa /P /dpubs /i%%f & echo - - - - - -)echo COMPLETEDif "%1

-- take the data below for example, we can see that records from 2 to 3 is 0, and records from 8 to 10 is also 0,-- and we want to answer this question using SQLdeclare @t table(seqno int identity

/*filter records with positions from M to N*/create table #test (val char(2))insert #test select a1insert #test select a2insert #test select a3insert #test select a4insert #test select a5ins

用image类型方法：1、建立过程CREATE PROCEDURE sp_textcopy (   @srvname    varchar (30),   @login      varchar (30),   @password    varchar (30),   @dbname      varchar (30),   @tbname      varchar (30),   @coln

/*可惜function里面不能使用sp_executesql(除非自己写一个相同功能的extended procedure),要不写成function更为自然*/IF OBJECT_ID(strcat) IS NOT NULL DROP PROCEDURE strcatGOCREATE PROCEDURE strcat @cTable NVar

/*给一个table的某个字段排序，分别用sub-query和self-join。这个例子会有很多变化，例如qty值是否有重复，关键的关系运算符是 >, >=, */declare @t table(qty int)insert @t select 740insert @t select 100insert @t select 560insert @t

 print replace(0x2027202729,0x20,print replace(0x2027202729,0x20,)

/* reduce the consecutive characters alike into single one WinXP, SQL Server 2K*/if object_id(fn_unicate) is not null drop function fn_unicategocreate function dbo.fn_unic

set nocount ongocreate table #cmd_output (seqno int identity(1,1), value1 nvarchar(512))create table #tasklist(image_name varchar(30), pid int, session_name varchar(100), session_id varchar

-- SQL Server 2000declare @str nvarchar(10)set @str = + stringprint + @strprint + string