tsql

TSQL – Get Number Of Cores & MaxDOP In Single Query

ben himself's picture

This is a handy script to show the number of cores and MaxDOP in a single output, along with a built-in script to change the value. The built-in script shows up as a query result so you can copy/paste it.

SELECT cpu_count AS [Cores] 
        , (SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism') AS [MaxDOP]
        , '/*Next Line For SQLCMD Mode*/
:CONNECT ' + @@SERVERNAME + '
sp_configure ''max degree of parallelism'', ''VALUE''; 
RECONFIGURE;
GO
        ' AS [Configure Script]
FROM [sys].[dm_os_sys_info]

Tags: 

Finding Duplicate Records In Database

ben himself's picture
SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK)
FROM TABLE_NAME
GROUP BY COL_TO_CHECK
HAVING COUNT(COL_TO_CHECK) > 1
If there is more than 1 column with that value it will show up like:
COL_TO_CHECK               COUNT(COL_TO_CHECK)
-------------------------------------------------------------
Value                             3
Value2                            2
Value99                           4

Tags: 

TSQL – Loop Through Result Set WITHOUT Cursors

ben himself's picture

There is a time and a place for everything, cursors included. Here is one way to avoid using them.

To NOT use cursors, you must create a temp table with a "flag" column to indicate whether or not it has been processed yet. Then, rather than updating the cursor, you update the flag column. Here's an example:

Tags: 

SQL Server - Download all RDL files from Report Server

ben himself's picture
---- Allow advanced options to be changed.
--EXEC sp_configure 'show advanced options', 1
--GO
---- Update the cuently configured value for advanced options.
--RECONFIGURE
--GO
---- Enable xp_cmdshell
--EXEC sp_configure 'xp_cmdshell', 1
--GO
---- Update the cuently configured value for xp_cmdshell
--RECONFIGURE
--GO
---- Disallow further advanced options to be changed.
--EXEC sp_configure 'show advanced options', 0
--GO
---- Update the cuently configured value for advanced options.
--RECONFIGURE
--GO
 
--Replace NULL with keywords of the ReportManager's Report Path,

Tags: 

Pages

Subscribe to RSS - tsql