ذخیره اتصالات به سرورهای بانک اطلاعاتی

If you're like me, you probably have a significant number of servers you access on a regular basis, which means every time you start up SQL Server Management Studio (SSMS) you have to log in to each server typing in user names and passwords and connecting one at a time. I find this very obnoxious and annoying.

Thankfully, SQL Server Management Studio allows you to save connections to various servers via Registered Servers, which can include Database Engines, Reporting Services, Integration Services, and Analysis Services. Registered servers allow you to easily check if a server is running, create groups of servers, use user-friendly names for your servers, and import/export your registered server groups.

To view and add a registered server, start up SSMS go to View > Registered Servers. Then in the Registered Servers pane, right-click Local Server Groups and click New Server Registration.

Then just enter the server name, select the authentication, key in the User name and Password if necessary, and check Remember password.

Now each time you start up SSMS, your registered servers will be viewable in the Registered Servers pane. Simply double-click the server you wish to navigate and that server will be opened within the Object Explorer.

Save Frequently Used Connections in SQL Server Management Studio

متوقف کردن اجرای پروسیجر در حال اجرا با کوئری

declare @nuke_spid int

select  @nuke_spid = session_id
from    sys.dm_exec_requests r outer
apply sys.fn_get_sql(r.sql_handle) s 
where   s.text like '%sanad_insert r%'

بعد از مقدار کردن متغیر nuke_spid@ با دستور kill اقدام به توقف نمائید.

مثلا اگر مقدار متغیر فوق 65 شد با دستور

kill 65

Stopping long running stored procedure from script

یافتن بزرگ ترین پدر فرزندی خاص

DECLARE @childID INT 
SET @childID  = 1 --chield to search

;WITH RCTE AS
(
    SELECT *, 1 AS Lvl FROM RelationHierarchy 
    WHERE ChildID = @childID

    UNION ALL

    SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
)
SELECT TOP 1 id, Name
FROM RCTE r
inner JOIN dbo.Person p ON p.id = r.ParentId
ORDER BY lvl DESC

رفع خطای INSERT failed because the following SET options have incorrect settings: ARITHABORT

So here is the scenario: you have a stored procedure which inserts new rows to a database table and you want to call it from your .NET code (in my case the application was a BizTalk orchestration using WCF-SQL adapters). You can successfully execute the procedure in SQL Server Management Studio, but when your code calls it, you get an error like this:

INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'

Resolving the error is pretty easy, you add the following line after the BEGIN clause in your stored procedure:

SET ARITHABORT ON; 

Queries failing because of the ARITHABORT setting

اتصال مقادیر یک ستون جدول در دستور group by

فرض کنید محتوای جدول بصورت زیر باشد:

id       Name       Value
1          A          4
1          B          8
2          C          9

اگر بخواهیم خروجی بصورت زیر باشد:

id          Column
1          A:4, B:8
2          C:9

بصورت زیر عمل میکنیم:

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

How to use GROUP BY to concatenate strings in SQL Server?

تغییر مسیر بانک tempdb

A. Moving the tempdb database

The following example moves the tempdb data and log files to a new location as part of a planned relocation.

 Note

Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.

1. Determine the logical file names of the tempdb database and their current location on the disk.

   SELECT name, physical_name AS CurrentLocation  
   FROM sys.master_files  
   WHERE database_id = DB_ID(N'tempdb');  
   GO  
   
   

2. Change the location of each file by using ALTER DATABASE.

   USE master;  
   GO  
   ALTER DATABASE tempdb   
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');  
   GO  
   ALTER DATABASE tempdb   
   MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');  
   GO  
   
   

3. Stop and restart the instance of SQL Server.

4. Verify the file change.

   SELECT name, physical_name AS CurrentLocation, state_desc  
   FROM sys.master_files  
   WHERE database_id = DB_ID(N'tempdb');  
   
   

5. Delete the tempdb.mdf and templog.ldf files from the original location.

Moving the tempdb database

وضعیت روال ها

SELECT TOP 10
        d.object_id ,
        d.database_id ,
        OBJECT_NAME(object_id, database_id) 'proc name' ,
        d.cached_time ,
        d.last_execution_time ,
        d.total_elapsed_time ,
        d.total_elapsed_time / d.execution_count AS [avg_elapsed_time] ,
        d.last_elapsed_time ,
        d.execution_count
FROM    sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;

sys.dm_exec_procedure_stats (Transact-SQL)

شرح آمار IO

USE Northwind
GO
--فعال سازی آمار مربوط به IO

SET STATISTICS IO ON
go
SELECT * FROM Orders

نتیجه مربوط به دستور فوق:

Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

ثبت گروهی در جداول master / detail بدون استفاده از کرسر

فرض کنیم دو جدول بشکل زیر را داریم:

جدول Factor

CREATE TABLE Factor(    [id] [BIGINT] IDENTITY(1,1) NOT NULL,    [salmali] [BIGINT] NOT NULL,    [shomare] [BIGINT] NOT NULL,    [tarikhe] [NVARCHAR](10) NOT NULL )

جدول جزییات Factor_Copy

CREATE TABLE Factor_Copy(    [id] [BIGINT] IDENTITY(1,1) NOT NULL,    [salmali] [BIGINT] NOT NULL,    [shomare] [BIGINT] NOT NULL,    [tarikhe] [NVARCHAR](10) NOT NULL )

اگر بخواهیم رکوردهای جدول Factor را در Factor_Copy درج کنیم کدی بشکل زیر مینویسیم

insert into Factor_Copy ( salmali, shomare, tarikhe )select salmali,shomare,tarikhefrom Factor

اگر بخواهیم کدهای identity ایجاد شده در جدول Factor_Copy و کد مرتبط با جدول Factor را بدست آوریم باید بشکل زیر عمل کنیم:

MERGE Factor_Copy as targetUSING    (        SELECT *        FROM    Factor        ) AS sourceON     (1=0) -- make sure the result is FalseWHEN NOT MATCHED BY TARGET THEN   insert  ( salmali, shomare, tarikhe)    VALUES (source.salmali,source.shomare,source.tarikhe)    OUTPUT $action,source.id, INSERTED.* INTO #tmp;    

How can I populate a table that must hold the linkbetween the id's of records in the old database table and the new database table?

تولید ترتیبی از اعداد یا تاریخ بدون استفاده از حلقه

1.

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

2.

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2

3.

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3