Öؽ¨ SQLServer Ë÷ÒýµÄÖØÒªÐÔ!
ÔÎÄת×Ô:http://dev.csdn.net/develop/article/71/71778.shtm
´ó¶àÊýSQL Server±íÐèÒªË÷ÒýÀ´Ìá¸ßÊý¾ÝµÄ·ÃÎÊËٶȣ¬Èç¹ûûÓÐË÷Òý£¬SQL ServerÒª½øÐбí¸ñɨÃè¶ÁÈ¡±íÖеÄÿһ¸ö¼Ç¼²ÅÄÜÕÒµ½Ë÷ÒªµÄÊý¾Ý¡£Ë÷Òý¿ÉÒÔ·ÖΪ´ØË÷ÒýºÍ·Ç´ØË÷Òý£¬´ØË÷Òýͨ¹ýÖØÅűíÖеÄÊý¾ÝÀ´Ìá¸ßÊý¾ÝµÄ·ÃÎÊËٶȣ¬¶ø·Ç´ØË÷ÒýÔòͨ¹ýά»¤±íÖеÄÊý¾ÝÖ¸ÕëÀ´Ìá¸ßÊý¾ÝµÄË÷Òý¡£
Ë÷ÒýµÄÌåϵ½á¹¹£º
ΪʲôҪ²»¶ÏµÄά»¤±íµÄË÷Òý£¿Ê×ÏÈ£¬¼òµ¥½éÉÜÒ»ÏÂË÷ÒýµÄÌåϵ½á¹¹¡£SQL ServerÔÚÓ²ÅÌÖÐÓÃ8KBÒ³ÃæÔÚÊý¾Ý¿âÎļþÄÚ´æ·ÅÊý¾Ý¡£È±Ê¡Çé¿öÏÂÕâЩҳÃæ¼°Æä°üº¬µÄÊý¾ÝÊÇÎÞ×éÖ¯µÄ¡£ÎªÁËʹ»ìÂÒ±äΪÓÐÐò£¬¾ÍÒªÉú³ÉË÷Òý¡£Éú³ÉË÷Òýºó£¬¾ÍÓÐÁËË÷ÒýÒ³ºÍÊý¾ÝÒ³£¬Êý¾ÝÒ³±£´æÓû§Ð´ÈëµÄÊý¾ÝÐÅÏ¢¡£Ë÷ÒýÒ³´æ·ÅÓÃÓÚ¼ìË÷ÁеÄÊý¾ÝÖµÇåµ¥£¨¹Ø¼ü×Ö£©ºÍË÷Òý±íÖиÃÖµËùÔڼͼµÄµØÖ·Ö¸Õë¡£Ë÷Òý·ÖΪ´ØË÷ÒýºÍ·Ç´ØË÷Òý£¬´ØË÷ÒýʵÖÊÉÏÊǽ«±íÖеÄÊý¾ÝÅÅÐò£¬¾ÍºÃÏñÊÇ×ÖµäµÄË÷ÒýĿ¼¡£·Ç´ØË÷Òý²»¶ÔÊý¾ÝÅÅÐò£¬ËüÖ»±£´æÁËÊý¾ÝµÄÖ¸ÕëµØÖ·¡£ÏòÒ»¸ö´ø´ØË÷ÒýµÄ±íÖвåÈëÊý¾Ý£¬µ±Êý¾ÝÒ³´ïµ½100%ʱ£¬ÓÉÓÚÒ³ÃæûÓпռä²åÈëеĵļͼ£¬Õâʱ¾Í»á·¢Éú·ÖÒ³£¬SQL Server ½«´óÔ¼Ò»°ëµÄÊý¾Ý´ÓÂúÒ³ÖÐÒƵ½¿ÕÒ³ÖУ¬´Ó¶øÉú³ÉÁ½¸ö°ëµÄÂúÒ³¡£ÕâÑù¾ÍÓдóÁ¿µÄÊý¾Ý¿Õ¼ä¡£´ØË÷ÒýÊÇË«ÏòÁ´±í£¬ÔÚÿһҳµÄÍ·²¿±£´æÁËÇ°Ò»Ò³¡¢ºóÒ»Ò³µØÖ·ÒÔ¼°·ÖÒ³ºóÊý¾ÝÒƶ¯µÄµØÖ·£¬ÓÉÓÚÐÂÒ³¿ÉÄÜÔÚÊý¾Ý¿âÎļþÖеÄÈκεط½£¬Òò´ËÒ³ÃæµÄÁ´½Ó²»Ò»¶¨Ö¸Ïò´ÅÅ̵ÄÏÂÒ»¸öÎïÀíÒ³£¬Á´½Ó¿ÉÄÜÖ¸ÏòÁËÁíÒ»¸öÇøÓò£¬Õâ¾ÍÐγÉÁ˷ֿ飬´Ó¶ø¼õÂýÁËϵͳµÄËٶȡ£¶ÔÓÚ´ø´ØË÷ÒýºÍ·Ç´ØË÷ÒýµÄ±íÀ´Ëµ£¬·Ç´ØË÷ÒýµÄ¹Ø¼ü×ÖÊÇÖ¸Ïò´ØË÷ÒýµÄ£¬¶ø²»ÊÇÖ¸ÏòÊý¾ÝÒ³µÄ±¾Éí¡£
ΪÁË¿Ë·þÊý¾Ý·Ö¿é´øÀ´µÄ¸ºÃæÓ°Ï죬ÐèÒªÖع¹±íµÄË÷Òý£¬ÕâÊǷdz£·ÑʱµÄ£¬Òò´ËÖ»ÄÜÔÚÐèҪʱ½øÐС£¿ÉÒÔͨ¹ýDBCC SHOWCONTIGÀ´È·¶¨ÊÇ·ñÐèÒªÖع¹±íµÄË÷Òý¡£ÏÂÃæ¾ÙÀýÀ´ËµÃ÷DBCC SHOWCONTIGºÍDBCC REDBINDEXµÄʹÓ÷½·¨¡£ÒÔSQL Server×Ô´øµÄnorthwindÊý¾Ý×÷ΪÀý×Ó
´ø¿ªSQL ServerµÄQuery analyzerÊäÈëÃüÁ
use pubs
declare @table_id int
set @table_id=object_id('tbldlvinfoback')
dbcc showcontig(@table_id)
Õâ¸öÃüÁîÏÔʾpubsÊý¾Ý¿âÖеÄtbldlvinfoback±íµÄ·Ö¿éÇé¿ö£¬½á¹ûÈçÏ£º
DBCC SHOWCONTIG ÕýÔÚɨÃè 'tblDlvInfoback' ±í...
±í: 'tblDlvInfoback'£¨1797581442£©£»Ë÷Òý ID: 0£¬Êý¾Ý¿â ID: 5
ÒÑÖ´ÐÐ TABLE ¼¶±ðµÄɨÃè¡£
- ɨÃ
Ïà¹ØÎĵµ£º
select
convert(char(4),auth,120)+'Äê'+
substring(convert(char(10),auth,120),6,2)+'ÔÂ'+
substring(convert(char(10),auth,120),9,2)+'ÈÕ',
convert(char(4),appr,120)+'Äê'+
substring(convert(char(10),appr,120),6,2)+'ÔÂ'+
substring(convert(char(10),appr,120),9,2)+'ÈÕ'
from a
ÒÔÉÏ´úÂëʵÏֵĹ¦Ä ......
ÈçºÎÔÚÒ»¸öûÓÐÖ÷¼üµÄ±íÖлñÈ¡µÚnÐÐÊý¾Ý£¬ÔÚsql2005ÖпÉÒÔÓÃrow_number£¬µ«ÊDZØÐëÖ¸¶¨ÅÅÐòÁУ¬·ñÔòÄã¾Í²»µÃ²»ÓÃselect intoÀ´¹ý¶Éµ½ÁÙʱ±í²¢Ôö¼ÓÒ»¸öÅÅÐò×ֶΡ£
ÓÃÓαêµÄfetch absoluteÓï¾ä¿ÉÒÔ»ñÈ¡¾ø¶ÔÐÐÊýϵÄijÐÐÊý¾Ý,²âÊÔ´úÂëÈçÏÂ:
set nocount on
--½¨Á¢²âÊÔ»·¾³²¢²åÈëÊý¾Ý£¬²¢ÇÒ±íûÓÐÖ÷¼ü
create table t ......
SQLServer2005·Ö½â²¢µ¼ÈëxmlÎļþ ÊÕ²Ø
²âÊÔ»·¾³SQL2005£¬windows2003
DECLARE @idoc int;
DECLARE @doc xml;
SELECT @doc=bulkcolumn from OPENROWSET(
BULK 'D: \test.xml',
SINGLE_BLOB) AS x
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc
......
±¾ÎÄÀ´×ÔCSDN²©¿Í£ºhttp://blog.csdn.net/jinjazz/archive/2008/07/14/2650506.aspx
½«oledb¶ÁÈ¡µÄexcelÊý¾Ý¿ìËÙ²åÈëµÄsqlserverÖУ¬ºÜ¶àÈËͨ¹ýÑ»·À´Æ´½Ósql£¬ÕâÑù×ö²»µ«ÈÝÒ׳ö´í¶øÇÒЧÂʵÍÏ£¬×îºÃµÄ°ì·¨ÊÇʹÓÃbcp£¬Ò²¾ÍÊÇSystem.Data.SqlClient.SqlBulkCopy ÀàÀ´ÊµÏÖ¡£²»µ«Ëٶȿ죬¶øÇÒ´úÂë¼òµ¥£¬ÏÂÃæ²âÊÔ´úÂëµ¼ÈëÒ»¸ö6 ......
SQLServer Öк¬×ÔÔöÖ÷¼üµÄ±í£¬Í¨³£²»ÄÜÖ±½ÓÖ¸¶¨IDÖµ²åÈ룬¿ÉÒÔ²ÉÓÃÒÔÏ·½·¨²åÈë¡£
1. SQLServer ×ÔÔöÖ÷¼ü´´½¨Óï·¨£º
identity(seed, increment)
ÆäÖÐ
seed Æðʼֵ
increment ÔöÁ¿
ʾÀý£º
create table student(
id int identity(1,1),
name varcha ......