اتصال مقادیر یک ستون جدول در دستور 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)