֮ǰÓÐÒ»¸öSQLServerµÄ·ÖÒ³´æ´¢¹ý³Ì µ«ÊÇÐÔÄܲ»ÊÇÊ®·ÖÀíÏë
ÓÖÕÒÁËÒ»¸ö
--SQL2005·ÖÒ³´æ´¢¹ý³Ì
/**
if exists(select * from sysobjects where name='fenye')
drop proc fenye
**/
CREATE procedure fenye
@tableName nvarchar(200) ,
@pageSize int,
@curPage int ,
@orderBy nvarchar(200) ,
@field nvarchar(200) = '*' ,
@condition nvarchar(200)
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max), -- SQL to execute
@recct int -- total # of records (for GridView paging interface)
IF LTRIM(RTRIM(@condition)) = '' SET @condition = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @field + 'from ' + @tableName +'WHERE ' + @condition + 'ORDER BY ' + ......
֮ǰÓÐÒ»¸öSQLServerµÄ·ÖÒ³´æ´¢¹ý³Ì µ«ÊÇÐÔÄܲ»ÊÇÊ®·ÖÀíÏë
ÓÖÕÒÁËÒ»¸ö
--SQL2005·ÖÒ³´æ´¢¹ý³Ì
/**
if exists(select * from sysobjects where name='fenye')
drop proc fenye
**/
CREATE procedure fenye
@tableName nvarchar(200) ,
@pageSize int,
@curPage int ,
@orderBy nvarchar(200) ,
@field nvarchar(200) = '*' ,
@condition nvarchar(200)
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max), -- SQL to execute
@recct int -- total # of records (for GridView paging interface)
IF LTRIM(RTRIM(@condition)) = '' SET @condition = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @field + 'from ' + @tableName +'WHERE ' + @condition + 'ORDER BY ' + ......
·½·¨Ò»¡¢¾¡Á¿Ê¹Óø´ÔÓµÄSQLÀ´´úÌæ¼òµ¥µÄÒ»¶Ñ SQL.
ͬÑùµÄÊÂÎñ£¬Ò»¸ö¸´ÔÓµÄSQLÍê³ÉµÄЧÂʸßÓÚÒ»¶Ñ¼òµ¥SQLÍê³ÉµÄЧÂÊ¡£Óжà¸ö²éѯʱ£¬ÒªÉÆÓÚʹÓÃJOIN¡£
oRs=oConn.Execute("SELECT * from Books")
while not oRs.Eof
strSQL = "SELECT * from Authors WHERE AuthorID="&oRs("AuthorID") oRs2=oConn.Execute(strSQL)
Response.write oRs("Title")&">>"&oRs2("Name")&"<br>&q uot;
oRs.MoveNext()
wend
Òª±ÈÏÂÃæµÄ´úÂëÂý£º
strSQL="SELECT Books.Title,Authors.Name from Books JOIN Authors ON Authors.AuthorID=Books.AuthorID"
oRs=oConn.Execute(strSQL)
while not oRs.Eof
Response.write oRs("Title")&">>"&oRs("Name")&"<br>&qu ot;
oRs.MoveNext()
wend
·½·¨¶þ¡¢¾¡Á¿±ÜÃâʹÓÿɸüРRecordset
oRs=oConn.Execute("SELECT * from Authors WHERE AuthorID=17",3,3)
oRs("Name")="DarkMan"
oRs.Update()
Òª±ÈÏÂÃæµÄ´úÂëÂý£º
strSQL = "UPDATE Authors SET Name='DarkMan' WHERE AuthorID=17"
oConn.Execute strSQL
·½·¨Èý¡¢¸üÐÂÊý¾Ý¿âʱ£¬¾¡Á¿²ÉÓÃÅú´¦ Àí¸üÐÂ
½«ËùÓеÄS ......
·½·¨Ò»¡¢¾¡Á¿Ê¹Óø´ÔÓµÄSQLÀ´´úÌæ¼òµ¥µÄÒ»¶Ñ SQL.
ͬÑùµÄÊÂÎñ£¬Ò»¸ö¸´ÔÓµÄSQLÍê³ÉµÄЧÂʸßÓÚÒ»¶Ñ¼òµ¥SQLÍê³ÉµÄЧÂÊ¡£Óжà¸ö²éѯʱ£¬ÒªÉÆÓÚʹÓÃJOIN¡£
oRs=oConn.Execute("SELECT * from Books")
while not oRs.Eof
strSQL = "SELECT * from Authors WHERE AuthorID="&oRs("AuthorID") oRs2=oConn.Execute(strSQL)
Response.write oRs("Title")&">>"&oRs2("Name")&"<br>&q uot;
oRs.MoveNext()
wend
Òª±ÈÏÂÃæµÄ´úÂëÂý£º
strSQL="SELECT Books.Title,Authors.Name from Books JOIN Authors ON Authors.AuthorID=Books.AuthorID"
oRs=oConn.Execute(strSQL)
while not oRs.Eof
Response.write oRs("Title")&">>"&oRs("Name")&"<br>&qu ot;
oRs.MoveNext()
wend
·½·¨¶þ¡¢¾¡Á¿±ÜÃâʹÓÿɸüРRecordset
oRs=oConn.Execute("SELECT * from Authors WHERE AuthorID=17",3,3)
oRs("Name")="DarkMan"
oRs.Update()
Òª±ÈÏÂÃæµÄ´úÂëÂý£º
strSQL = "UPDATE Authors SET Name='DarkMan' WHERE AuthorID=17"
oConn.Execute strSQL
·½·¨Èý¡¢¸üÐÂÊý¾Ý¿âʱ£¬¾¡Á¿²ÉÓÃÅú´¦ Àí¸üÐÂ
½«ËùÓеÄS ......
--MyDBΪÐÞ¸´µÄÊý¾ÝÃû
USE MASTER
GO
SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE MyDB SET EMERGENCY
GO
sp_dboption 'MyDB', 'single user', 'true'
GO
DBCC CHECKDB('MyDB','REPAIR_ALLOW_DATA_LOSS')
GO
ALTER DATABASE MyDB SET ONLINE
GO
sp_configure 'allow updates', 0 reconfigure with override
GO
sp_dboption 'MyDB', 'single user', 'false'
GO
DBCC CHECKDB('MyDB','REPAIR_ALLOW_DATA_LOSS')
GO
¿ÉÒÔ²»Ö´ÐÐÒ²Äܳɹ¦ ......
Table-Naming Standards
Table-naming standards, as well as any standard within a business, are critical to
maintaining control. After studying the tables and data in the previous sections, you
probably noticed that each table’s suffix is _TBL. This is a naming standard selected
for use, such as what’s been used at various client sites. The _TBL simply tells you
that the object is a table; there are many different types of objects in a relational
database. For example, you will see that the suffix _INX is used to identify indexes
on tables in later hours. Naming standards exist almost exclusively for overall
organization and assist immensely in the administration of any relational database.
Remember, the use of a suffix is not mandatory when naming database objects. ......
1¡¢ÎļþºÍÎļþ×éµÄº¬ÒåÓë¹Øϵ
ÿ¸öÊý¾Ý¿âÓÐÒ»¸öÖ÷Êý¾ÝÎļþ.ºÍÈô¸É¸ö´ÓÎļþ¡£ÎļþÊÇÊý¾Ý¿âµÄÎïÀíÌåÏÖ¡£ Îļþ×é¿ÉÒÔ°üÀ¨·Ö²¼ÔÚ¶à¸öÂß¼·ÖÇøµÄÎļþ£¬ÊµÏÖ¸ºÔØƽºâ¡£Îļþ×éÔÊÐí¶ÔÎļþ½øÐзÖ×飬ÒÔ±ãÓÚ¹ÜÀíºÍÊý¾ÝµÄ·ÖÅ䣯·ÅÖá£ÀýÈ磬¿ÉÒÔ·Ö±ðÔÚÈý¸öÓ²ÅÌÇý¶¯Æ÷ÉÏ´´½¨Èý¸öÎļþ£¨Data1.ndf¡¢Data2.ndf ºÍ Data3.ndf£©£¬²¢½«ÕâÈý¸öÎļþÖ¸Åɵ½Îļþ×é fgroup1 ÖС£È»ºó£¬¿ÉÒÔÃ÷È·µØÔÚÎļþ×é fgroup1 ÉÏ´´½¨Ò»¸ö±í¡£¶Ô±íÖÐÊý¾ÝµÄ²éѯ½«·ÖÉ¢µ½Èý¸ö´ÅÅÌÉÏ£¬Òò¶øÐÔÄܵÃÒÔÌá¸ß¡£ÔÚ RAID£¨´ÅÅÌÈßÓàÕóÁÐ)Ìõ´ø¼¯ÉÏ´´½¨µ¥¸öÎļþÒ²¿ÉÒÔ»ñµÃÏàͬµÄÐÔÄܸÄÉÆ¡£È»¶ø£¬ÎļþºÍÎļþ×éʹÄúµÃÒÔÔÚдÅÅÌÉÏÇáÒ×µØÌí¼ÓÐÂÎļþ¡£ÁíÍ⣬Èç¹ûÊý¾Ý¿â³¬¹ýµ¥¸ö Microsoft Windows ÎļþµÄ×î´ó´óС£¬Ôò¿ÉÒÔʹÓôÎÒªÊý¾ÝÎļþÔÊÐíÊý¾Ý¿â¼ÌÐøÔö³¤¡£
2¡¢Îļþ¡¢Îļþ×éÔÚʵ¼ùÓ¦ÓÃÖг£¼ûµÄÎÊÌâ
ͨ³£Çé¿öÏÂÎÒÃǹ¹ÔìµÄÊý¾Ý¿â¶¼Ö»ÓÐÁ½¸öÎļþ,mdfÎļþºÍldfÎļþ.µ«ÊÇÕâÑùÓÐÁ½¸öȱµã£º
(Ò»)ÈÝÒ×µ¼ÖÂÎļþ¹ý´ó
ÎÒÃÇÖªµÀ,mdfÎļþÊÇÊý¾Ý¿âÎļþ,ÕâÑùµÄ»°Ò²¾ÍÒâζ×ÅËæ×ÅÊý¾ ......
ÔÚÊý¾Ý¿âÓ¦ÓõÄÉè¼ÆÖУ¬ÎÒÃÇÍùÍù»áÐèÒª»ñȡijЩ±íµÄ¼Ç¼×ÜÊý£¬ÓÃÓÚÅжϱíµÄ¼Ç¼×ÜÊýÊÇ·ñ¹ý´ó£¬ÊÇ·ñÐèÒª±¸·ÝÊý¾ÝµÈ¡£ÎÒÃÇͨ³£µÄ×ö·¨ÊÇ£ºselect count(*) as c from tableA ¡£È»¶ø¶ÔÓڼǼÊý¾Þ´óµÄ±í£¬ÉÏÊö×ö·¨½«»á·Ç³£ºÄʱ¡£ÔÚDELL 4400 ·þÎñÆ÷ÉÏ×öÊÔÑ飬MS Sqlserver 2000 Êý¾Ý¿â¶ÔÓÚ100Íò¼Ç¼µÄ¼òµ¥Êý¾Ý±íÖ´ÐÐÉÏÊöÓï¾ä£¬Ê±¼äÔÚ1·ÖÖÓÒÔÉÏ¡£Èç¹ûÔÚ±íµÄij¸ö×Ö¶ÎÉÏ×ö¾Û´ØË÷Òý£¬µÚÒ»´ÎÖ´ÐиÃÓï¾äµÄʱ¼äºÍûÓÐË÷ÒýµÄʱ¼ä²î²»¶à£¬Ö®ºóÖ´ÐÐÉÏÊöÓï¾ä£¬ËٶȺܿ죬ÔÚ1ÃëÖÐÒÔÄÚ£¬µ«µ±±íµÄ¼Ç¼Êý·¢Éú½Ï´ó±ä»¯ºó£¬ÔÙÖ´ÐиÃÓï¾äÓÖ»á¾ÀúÒ»´ÎºÄʱµÄ¹ý³Ì¡£¶øÇÒ²»ÊÇÿ¸ö±í¶¼ÊʺÏ×ö¾Û´ØË÷ÒýµÄ£¬¶ÔÓÚÊýÁ¿¾Þ´óµÄ±í£¬Èç¹ûÐèÒª¾³£Ôöɾ²Ù×÷£¬½¨¾Û´ØË÷ÒýÊÇÒ»¸öºÜ²»Ã÷ÖǵÄ×ö·¨£¬½«»á¼«´óµÄÓ°ÏìÔöɾµÄËٶȡ£ÄÇôÓÐûÓÐÒ»¸ö±È½Ï¼òµ¥µÄ·½·¨¿ìËÙ»ñÈ¡±íµÄ¼Ç¼×ÜÊýÄØ£¿´ð°¸ÊÇÓеġ£
ÔÚMS SQL Êý¾Ý¿âÖÐÿ¸ö±í¶¼ÔÚsysindexes ϵͳ±íÖÐÓµÓÐÖÁÉÙÒ»Ìõ¼Ç¼£¬¸Ã¼Ç¼ÖеÄrows ×ֶλᶨʱ¼Ç¼±íµÄ¼Ç¼×ÜÊý¡£
ÏÂÃæÊÇsysindexes ±íµÄÏà¹Ø¼Ç¼µÄº¬Ò壺
ÁÐÃû Êý¾ÝÀàÐÍ ÃèÊö
id int ±íID(Èç¹û indid = 0 »ò255)¡£·ñÔòΪË÷ÒýËùÊô±íµÄID
Indid smallint Ë÷ÒýID£º
0£½±í
1£½¾Û´ØË÷Òý
>1£½·Ç¾Û´ØË÷Òý
255£ ......