create table students (  name varchar(25),  class varchar(25),  grade int)insert into students values (张三,语文,20)insert into students values (张三,数学,90)insert into students values

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database<br />http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/<br />August 23, 2008 by pinaldave<br />Todayin this article I wo

(1) 方法一SELECT O.NAME,I.ROWSFROM SYSOBJECTS O INNER JOIN SYSINDEXES I ON O.ID=I.IDWHERE i.INDIDORDER BY O.NAME (2)方法二 待补充...

Rebuild indexes online with SQL Server 2005http://blogs.techrepublic.com.com/datacenter/?p=249Online index rebuildSQL Server 2005 introduces the ability to rebuild your indexes in anonline f

bulk insert语句和bcp实用工具http://www.it118.org/specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/8bf47df8-cad8-4d97-9a54-ba461a9c017a.htmbulk insert语句和bcp实用工具用于在SQL SERVER 数据库和数据文件之间复制数据.(ps:关于导入和导出数据的更多使用方法,可以参考《sql server联机丛书》的 “管理 SQL Server ”-->“导入和导出数据”章节)bulk

http://stackoverflow.com/questions/663040/query-several-eav-attributes-in-separate-columnshttp://structureddata.org/2009/03/19/the-impact-of-good-table-and-query-design/SELECT t.TicketID,    MAX(CONCAT(p.FirstName, ' ', p.LastName)) AS RequesterFullName,

<br />实例:<br />(来自：http://space.itpub.net/16436858/viewspace-625445）<br />我们将创建一个员工表和一个名为ReportsTo的自引用字段,其引用回Emloyee_ID,然后编写一个查询,其返回像Stephen(Employee_ID=2)报告的所有员工以及向Stephen的下属报告的所有员工.<br />代码清单:<br /> <br />表结构：<br />Employee_NM                          

<br />When we are going to restart SQL DB service with cluster, we need to follow 2 steps:<br /> <br />1 restart service from service.msc<br /> <br /><br /> <br /> <br /> <br />2 Put service or application online<br /> <br />

1. Create table with identity columnCREATE TABLE tb(  id INT IDENTITY ,  name VARCHAR(100) );insert into tb values ('aa')GO 102. Insert into table tb (-1,'bb') - ignore idenity --set identity_insert tablename onset identity_insert tb ongoin

Which type(s) of replication do you need?http://www.cryer.co.uk/brian/sqlserver/howtoreplication.htm#TypeNeedThe three different types of replication (merge, snapshot and transaction) cannot be mixed within a publication, i.e. for each publicat

6 Different Ways To Get The Current Identity ValueFrom Wikihttp://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value<br />Jump to: navigation, search<br />Thistip will show you how to get the current identity value from a

<br />http://www.sql-server-performance.com/articles/dba/peer-to-peer_replication_p1.aspx<br /> <br />Replication is an important technology that can be used in SQLServer. With replication, a DBA can synchronize data between two ormore databases. This

<br /> <br />Before restore our database to the point in time, we need to make sure at least :<br /> our database recovery model is full we do have a latest database full backupwe do have a few transaction log backup<br />For #3, we can use Maintenance pla

http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html<br />There are several characters that have special meaning within aSQL query, for example the percent sign (%) in a LIKE query is awildcard that es

<br />http://www.sqlservercurry.com/2010/09/block-ip-addresses-to-sql-server-using.html<br /> <br />We were testing a scenario and wanted to block SQLServer connection through certain IP addresses. Here’s how we solvedthe requirement using a Logon Trig

<br />SQL Bulk insert from Excel 2003 - Microsoft.Jet.OLEDB.4.0<br /> <br />--http://www.sqlteam.com<br />1 Export data to existing EXCEL file from SQL Server table<br />insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', <br /> 'Excel 8.0;Database=D:

已知字符,求字符串位置 SELECT charindex(est, testing)  -----------2(1 row(s) affected)  已知字符位置,求字符串 SELECT SUBSTRING(testing,1,3) ----tes(1 row(s) affected) 

查询整个数据库,有没有以 prod 开头的字段:  exec sp_MSforeachtableSELECT * FROM sys.columnsWHERE OBJECT_ID=OBJECT_ID(?)AND name LIKE %Prod%

EXEC xp_cmdshell sqlcmd -S + @DBServerName + -d + @DBName + -i + @FilePathName  http://stackoverflow.com/questions/241925/is-it-possible-to-execute-a-text-file-from-sql-query

if not object_id(Tempdb..#T) is null    drop table #TGoCreate table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))Insert #Tselect 1,NA,NA1 union allselect 2,NA,NA2 union all

方法(1) SELECT stuff((select ,+ltrim(ColumnName) from #A for xml path()),1,1,)  例子： create table db(   id int)goDECLARE @i AS INT=0;WHILE @iBEGIN    insert in

IF object_id(tb) is not null    drop table tbcreate table tb(   PROD_ID int,   name VARCHAR(100))goINSERT INTO tbSELECT 111111,aaUNION ALLSELECT 111112,bbUNION ALLSEL

 找出TABLE1中Column1 满足value1 或者Column2 满足value2 或者Column3 满足value3的纪录  SELECT top 10 * FROM TABLE1WHERE CASE WHEN COLUMN1=value1 THEN 1 ELSE (CASE WHEN COLUMN2=value2 THE

--测试表create table Test(F1 varchar(10), F2 varchar(10))--插入数据insert into Test select 'jack' F1,'book1' F2unionselect 'jack' F1,'book2' F2unionselect 'jack' F1,'book3' F2unionselect 'Mary' F1,'book4' F2unionselect 'Mary' F1,'book5' F2unionselect 'Mike' F

-- disable all constraintsEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"-- delete data in all tablesEXEC sp_MSForEachTable "DELETE FROM ?"-- enable all constraintsexec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

CREATE TABLE AAAA(  id int,  salary numeric(16,2))GOINSERT INTO AAAA(ID) VALUES (1)INSERT INTO AAAA(ID) VALUES (2)INSERT INTO AAAA(ID) VALUES (3)INSERT INTO AAAA(ID) VALUES (4)INSE

 create table tb(  id int, --employee id  starttime datetime --log time)goINSERT INTO tb values (1,2010-05-02 00:00:01)INSERT INTO tb values (1,2010-05-02 00:10:01)INSERT INTO

Determining Backup or Restore Percent Complete in SQL Server 2008 and SQL Server 2005 If you want to see how far along your restore or backup is then

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->-- 比较两个数据库中表的差异-- u表，p存储过程,v视图-- INTFSIMSNEW新库，INTFSIMS旧库SELECT

<br />'Whether we should use static or dynamic SQL statements does NOT relate to an IF statement.<br />When choosing between static / dynamic SQL statements,you are suggested to use static SQL statements (usp_ListWorkOrder02)for most of the cases.

一般来说，在.net 里面要创建sqlpolygon实例，对组成polygon的点的顺序有着严格的要求，必须是逆时针方向的，例如：string wktString = "POLYGON((-74.00751113891601 40.73412061435751,-74.02193069458008 40.705758069466754,-73.96940231323242 40.72891

Speed Up SELECT DISTINCT Queries By : Neil BoyleJun 30, 2002  http://www.sql-server-performance.com/articles/per/select_distinct_queries_p

SQL 随机数 select  floor(rand()*100); --求随机数据 Solution 1 : SELECT TOP 1 * FROM [tablename] ORDER BY NEWID() Solution 2 :Declare @ID INTSELECT @ID=floor(rand()*100) SELECT TOP

Indexedviews can be created in any edition of SQL Server 2005. In SQL Server2005 Enterprise Edition, the query optimizer automatically considersthe indexed view. To use an indexed view in all other editions, theNOEXPAND table hint must be used.Refr