Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

MSSQL¾­µäÓï¾ä

MSSQL¾­µäÓï¾ä
1.°´ÐÕÊϱʻ­ÅÅÐò:Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.Êý¾Ý¿â¼ÓÃÜ:select encrypt('ԭʼÃÜÂë')
select pwdencrypt('ԭʼÃÜÂë')
select pwdcompare('ԭʼÃÜÂë','¼ÓÃܺóÃÜÂë') = 1--Ïàͬ£»·ñÔò²»Ïàͬ encrypt('ԭʼÃÜÂë')
select pwdencrypt('ԭʼÃÜÂë')
select pwdcompare('ԭʼÃÜÂë','¼ÓÃܺóÃÜÂë') = 1--Ïàͬ£»·ñÔò²»Ïàͬ
3.È¡»Ø±íÖÐ×Ö¶Î:declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='±íA'
set @sql='select '+right(@list,len(@list)-1)+' from ±íA'
exec (@sql)
4.²é¿´Ó²ÅÌ·ÖÇø:EXEC master..xp_fixeddrives
5.±È½ÏA,B±íÊÇ·ñÏàµÈ:if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print 'ÏàµÈ'
else
print '²»ÏàµÈ'
6.ɱµôËùÓеÄʼþ̽²ìÆ÷½ø³Ì:DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) from master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL ʼþ̽²éÆ÷')
EXEC sp_msforeach_worker '?'
7.¼Ç¼ËÑË÷:¿ªÍ·µ½NÌõ¼Ç¼
Select Top N * from ±í
-------------------------------
Nµ½MÌõ¼Ç¼(ÒªÓÐÖ÷Ë÷ÒýID)
Select Top M-N * from ±í Where ID in (Select Top M ID from ±í) Order by ID Desc
----------------------------------
Nµ½½áβ¼Ç¼
Select Top N * from ±í Order by ID Desc
8.ÈçºÎÐÞ¸ÄÊý¾Ý¿âµÄÃû³Æ:sp_renamedb 'old_name', 'new_name'
9£º»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§±í
select Name from sysobjects where xtype='u' and status>=0
10£º»ñȡijһ¸ö±íµÄËùÓÐ×Ö¶Î
select name from syscolumns where id=object_id('±íÃû')
11£º²é¿´Óëijһ¸ö±íÏà¹ØµÄÊÓͼ¡¢´æ´¢¹ý³Ì¡¢º¯Êý
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%±íÃû%'
12£º²é¿´µ±Ç°Êý¾Ý¿âÖÐËùÓд洢¹ý³Ì
select name as ´æ´¢¹ý³ÌÃû³Æ from sysobjects where xtype='P'
13£º²éѯÓû§´´½¨µÄËùÓÐÊý¾Ý¿â
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
»òÕß
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14£º²éѯijһ¸ö±í


Ïà¹ØÎĵµ£º

MSSQL ·Ö²¼Ê½ÊÂÎñ¹ÜÀí

   µ±Êý¾Ý·þÎñÆ÷ºÍWeb·þÎñÆ÷²¿ÊðÔÚ²»Í¬µÄ·þÎñÆ÷ÉÏʱ£¬»áÓõ½·Ö²¼Ê½ÊÂÎñ£¬ÐèÒª¶ÔÁ½¸ö·þÎñÆ÷µÄMSDTC½øÐÐÅäÖá£
    ´ò¿ª“¹ÜÀí¹¤¾ß¨D¨D×é¼þ·þÎñ”£¬ÒÔ´Ë´ò¿ª“×é¼þ·þÎñ¨D¨D¼ÆËã»ú”£¬ÔÚ“ÎҵĵçÄÔ”Éϵã»÷ÓÒ¼ü¡£ÔÚMSDTCÑ¡ÏÖУ¬µã»÷“°²È«ÅäÖÔ°´Å¥¡£ ......

How is MSSQL using memory?


How is MSSQL using memory?
http://www.sqlhacks.com/Administration/Memory-Usage
Memory is one the most important factor affecting MSSQL performance.
As an administrator, you should be monitoring the memory
regularly. When Microsoft SQL Server runs out of memory, it will use
virtual memory: ie: ......

ÓÃ×÷ҵʵÏÖ×Ô¶¯±¸·ÝMSSQLÊý¾Ý¿âµ½Ô¶³Ì·þÎñÆ÷

--´Ë´úÂëʵÏÖSQLÊý¾Ý¿âÔ¶³Ì±¸·Ý£¬·Åµ½×÷ÒµÀïÃæÖ´ÐпÉÒÔ×Ô¶¯±¸·ÝÊý¾Ý¿â¡¢×Ô¶¯É¾³ý@keepNDaysÌìÇ°±¸·Ý¡£
--´Ë´úÂ뽫±¾µØËùÓеÄÓû§Êý¾Ý¿â±¸·Ýµ½¹²ÏíĿ¼¡°\\backupServerIp\ShareName\Êý¾Ý¿â±¸·Ý¡±Ï¡£
--²¢É¾³ýÌìÇ°µÄ±¸·ÝÎļþ¡£Òª±¸·Ý³É¹¦±ØÐëÄܹ»¶Ô¹²ÏíĿ¼ÓвÙ×÷ȨÏÞ£¡

sp_configure 'xp_cmdshell',1 ......

MSSQLÊý¾Ý¿â²Ù×÷ʹÓÃÓï¾ä


1.°´ÐÕÊϱʻ­ÅÅÐò: 
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 
2.·ÖÒ³SQLÓï¾ä 
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from ±íÃû As tab) As t where rownum between ÆðʼλÖà And ½áÊøλÖà
3.»ñÈ¡µ±Ç°Êý¾ ......

ÆôÓÃMSSQL·Ö²¼Ê½ÊÂÎñµÄ³£¼ûÎÊÌâ

ÈçºÎ´´½¨Á´½Ó·þÎñÆ÷
IF  EXISTS (SELECT srv.name from sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Á´½Ó·þÎñÆ÷Ãû')
EXEC master.dbo.sp_dropserver @server=N'Á´½Ó·þÎñÆ÷Ãû'', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver
 @server = N'Á´½Ó·þÎñÆ÷Ãû'', @srvproduct= ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ