SQLË÷Òý¸ß¼¶Ó¦ÓÃ
--½áºÏsys.indexesºÍsys.index_columns,sys.objects,sys.columns²éѯË÷ÒýËùÊôµÄ±í»òÊÓͼµÄÐÅÏ¢
select
o.name as ±íÃû,
i.name as Ë÷ÒýÃû,
c.name as ÁÐÃû,
i.type_desc as ÀàÐÍÃèÊö,
is_primary_key as Ö÷¼üÔ¼Êø,
is_unique_constraint as Î¨Ò»Ô¼Êø,
is_disabled as ½ûÓÃ
from
sys.objects o
inner join
sys.indexes i
on
i.object_id=o.object_id
inner join
sys.index_columns ic
on
ic.index_id=i.index_id and ic.object_id=i.object_id
inner join
sys.columns c
on
ic.column_id=c.column_id and ic.object_id=c.object_id
go
--²éѯË÷ÒýµÄ¼üºÍÁÐÐÅÏ¢
select
o.name as ±íÃû,
i.name as Ë÷ÒýÃû,
c.name as ×ֶαàºÅ,
from
sysindexes i inner join sysobjects o
on
i.id=o.id
inner join
sysindexkeys k
on
o.id=k.id and i.indid=k.indid
inner join
syscolumns c
on
c.id=i.id and k.colid=c.colid
where
o.name='±íÃû'
--²éѯÊý¾Ý¿âdbÖбítbµÄËùÓÐË÷ÒýµÄËæÆ¬Çé¿ö
use db
go
select
a.index_id,---Ë÷Òý±àºÅ
b.name,---Ë÷ÒýÃû³Æ
avg_fragmentation_in_percent---Ë÷ÒýµÄÂß¼Ë鯬
from
sys.dm_db_indx_physical_stats(db_id(),object_id(N'create.consume'),null,null,null) as a
join
sys.indexes as b
on
a.object_id=b.object_id
and
a.index_id=b.index_id
go
---½âÊÍÏÂsys.dm_db_indx_physical_statsµÄ²ÎÊý
datebase_id: Êý¾Ý¿â±àºÅ£¬¿ÉÒÔʹÓÃdb_id()º¯Êý»ñȡָ¶¨Êý¾Ý¿âÃû¶ÔÓ¦µÄ±àºÅ¡£
object_id: ¸ÃË÷ÒýËùÊô±í»òÊÔͼµÄ±àºÅ
index_id: ¸ÃË÷ÒýµÄ±àºÅ
partition_number:¶ÔÏóÖзÖÇøµÄ±àºÅ
mode:ģʽÃû³Æ,ÓÃÓÚÖ¸¶¨»ñȡͳ¼ÆÐÅÏ¢µÄɨÃè¼¶±ð¡£
ÓйØsys.dm_db_indx_physical_statsµÄ½á¹û¼¯ÖеÄ×Ö¶ÎÃûÈ¥²éÏÂÁª»ú´ÔÊé¡£
---Ë÷ÒýÊÓͼ
Ë÷ÒýÊÓͼÊǾßÌ廯µÄÊÓͼ
--´´½¨Ë÷ÒýÊÓͼ
create view ÊÓͼÃû with schemabinding
as
select Óï¾ä
go
---´´½¨Ë÷ÒýÊÓͼÐèҪעÒâµÄ¼¸µã
1. ´´½¨Ë÷ÒýÊÓͼµÄʱºòÐèÒªÖ¸¶¨±íËùÊôµÄ¼Ü¹¹
--´íÎóд·¨
create view v_f with schemabinding
as
select
a.a,a.b,b.a,b.b
from
&nbs
Ïà¹ØÎĵµ£º
1. Ïû³ýtriggerµÄǶÌ×µ÷Óá£×îºÃ²»ÒªÓà EXEC sp_configure 'nested triggers', '0'£¬ Ó¦¸ÃÔÚtriggerÖÐʹÓÃÅжÏÓï¾ä£¬ ÀýÈ磺if not update (name) return¡£
2. ʹÓà not for replication ½ûÖ¹ÔÚ¸´ÖƵÄʱºò´¥·¢trigger¡£
3. ´´½¨publisher articleµÄʱºò£¬ ÉèÖà copy user triggersΪ true¡£
ÕâÑù±£Ö¤£ºtrigger²»»áǶÌ×µ÷ ......
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
¡¡¡¡1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿) ......
----start
SQL(Structured Query Language)£¬Ò²¾ÍÊǽṹ»¯²éѯÓïÑÔ£¬Ëü±»Éè¼ÆÓÃÀ´²Ù×÷¼¯ºÏµÄ£¬ÊǷǹý³Ì»¯µÄÓïÑÔ¡£Ëæ×ÅÓ¦ÓóÌÐòµÄ·¢Õ¹£¬ÒµÎñÂß¼Ô½À´Ô½¸´ÔÓ£¬´«Í³µÄSQLÒѾ²»ÄÜÂú×ãÈËÃǵÄÒªÇó£¬ÓÚÊÇÈËÃǶÔSQL½øÐÐÁËÀ©Õ¹£¬Ê¹Ëü¾ßÓÐÁ˹ý³Ì»¯µÄÂß¼£¬¼´£ºSQL PL¡£SQL PLµÄÈ«³ÆÊÇ SQL Procedural Language£ ......
Introduction to the SQL Server 2008 Resource Governor
Database Administration, Performance Tuning | January 4, 2010 | 4:05 pm
This is an excerpt from my free eBook, Brad’s Sure Guide to SQL Server 2008.
I think most of us are familiar with this situation: a SQL Server database is the bac ......