hank

  用substring (str,pos,len)-> 由 <str> 中的第 <pos> 位置開始，選出接下去的 <len> 個字元。 搭配PATINDEX( '%pattern%' , expression ) :指定運算式中第一次出現的起始位置  pattern  : 要搜尋的 key word，前後需要用 %%包起來  expression  : 被搜尋的內容  return  : int，搜尋內容的位置    '%[^0-9]%'-->不是數字0-9以外的字串   '%[0-9]%'-->數字0-9的字串 指令如下: select ltrim(SUBSTRING(address, PATINDEX('%[^0-9]%', address), LEN(address))) FROM customer

-- 查詢缺少INDEX的命令 SELECT DatabaseName = DB_NAME(database_id) ,[Number Indexes Missing] = count(*) FROM sys.dm_db_missing_index_details GROUP BY DB_NAME(database_id) ORDER BY 2 DESC; -- 查詢缺少INDEX的項目 SELECT TOP 10 [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) , avg_user_impact , TableName = statement , [EqualityUsage] = equality_columns , [InequalityUsage] = inequality_columns , [Include Cloumns] = included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY [Total Cost] DESC; 查出缺少的INDEX建議後,用下面的命令建立INCLUDE( 涵蓋索引 ) create index  idx3  on  Person.Person(MiddleName,EmailPromotion) include (FirstName,LastName,Title,PersonType) equality_columns用法 CREATE NONCLUSTERED INDEX IX_dbo_My_Table__Float_Filtered ON dbo . My_Table ( my_...

在資料庫名稱上按[右鍵]-->選[Facet]-->[AdHocRemoteQueriesEnabale]-->設為TRUE，才可使用opendatabase及openRowset

--連結不同資料庫,做INSERT的動作,個資做掩碼處理,錯誤時利用SQLMAIL發MAIL declare @cc varchar(max); declare @myCursor cursor; set @myCursor =cursor fast_forward FOR --先找有重複的資料數目 select (SELECT (count(reg_rno))  FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=999.999.999.999;User ID=@@@@@;Password=#####' ).[資料庫名稱].dbo.table_name where reg_rno is not null and len(reg_rno)>0 and lay_off=0 group by reg_rno having count(reg_rno)>1) open @myCursor  fetch next from @mycursor  into @cc  if ( @cc is null) --沒有重複的資料 BEGIN --做清空TABLE的動作 truncate table [資料庫名稱].dbo.TABLE_NAME ; --把資料insert進去 insert into [資料庫名稱].dbo.TABLE_NAME (AG_CODE,AG_NAME,EMAIL,REMOVE_YN,CREATE_DT,CREATE_BY,MODIFY_DT,MODIFY_BY) SELECT reg_rno ,case len(LTRIM(name)) when 1 then LTRIM(name) when 2 then left(ltrim(name),1)+'O' ELSE left(ltrim(name),1)+REPLICATE('O',len(ltrim(name))-2)+right(ltrim(name),1) END ,email,(case lay_off when 0 then 'N' ...