Find and remove duplicate rows - WITH CTE

with CTE as (

select

 col1
      ,col2
      ,col3
      ,col4
      ,col5
      ,col6

  ,RN = ROW_NUMBER()OVER(PARTITION BY  col1 ,col2  ORDER BY col1,col2)

  FROM [database].[schema].[table]
)
--select * from cte where RN >1

delete from cte where RN >1

Find only data between quotes (')

 select


SUBSTRING(RIGHT(inputtext,LEN(inputtext)-CHARINDEX('''',inputtext))
,0, CHARINDEX('''', RIGHT(inputtext,LEN(outputtext)-CHARINDEX('''',inputtext)) ) )

 from table

Find Useful information about your SQL Server

The below will find various useful chunks of information  about SQL Including licence information, version and cluster.  All using the SERVERPROPERTY command.

Run this:-


SELECT
@@SERVERNAME as InstanceName,


SERVERPROPERTY('BuildClrVersion')
,
 SERVERPROPERTY('Collation')
,
 SERVERPROPERTY('CollationID')
,
 SERVERPROPERTY('ComparisonStyle')
,
 SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
,
 SERVERPROPERTY('Edition')
,


 SERVERPROPERTY('InstanceName')
,
 SERVERPROPERTY('IsClustered')
,
 SERVERPROPERTY('IsFullTextInstalled')
,
 SERVERPROPERTY('IsIntegratedSecurityOnly')
,
 SERVERPROPERTY('IsSingleUser')
,
 SERVERPROPERTY('LCID')
,
 SERVERPROPERTY('LicenseType')
,
 SERVERPROPERTY('MachineName')
,
 SERVERPROPERTY('NumLicenses')
,
 SERVERPROPERTY('ProcessID')
,
 SERVERPROPERTY('ProductVersion')
,
 SERVERPROPERTY('ProductLevel')
,
 SERVERPROPERTY('ResourceLastUpdateDateTime')
,
 SERVERPROPERTY('ResourceVersion')
,
 SERVERPROPERTY('ServerName')
,
 SERVERPROPERTY('SqlCharSet')
,
 SERVERPROPERTY('SqlCharSetName')
,
 SERVERPROPERTY('SqlSortOrder')
,
 SERVERPROPERTY('SqlSortOrderName')
,
 SERVERPROPERTY('FilestreamShareName')
,
 SERVERPROPERTY('FilestreamConfiguredLevel')
,
 SERVERPROPERTY('FilestreamEffectiveLevel')

Find where Password and Username are the same

Weak security is always going to bite you.  The first thing any Hacker, Auditor, Penetration Tester or RAS is going to try is passwords that are the same as the user.

So how can we check this?  well a handy little script that uses PwdCompare, to checked the hashed password with the user name.

here it is:-

use master

select
cast(@@SERVERNAME as varchar(150)) as SQLInstanceName
,name as [LoginName]
,'Password is same as Login Name' [Description]
from syslogins
WHERE PWDCOMPARE (name,password) = 1

Finding the Active SQL Node

Ever needed to know what node of your SQL cluster is the alive one.  Wonder no more.

SQL:-

Select ServerProperty('ComputerNamePhysicalNetBIOS')

Will show all nodes and there current state.

Or in Powershell:-

# Set cluster name 
$cluster_name = "ClusterName";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $cluster_name;
# Get server properties
$properties = $srv.Properties
$owner_node = $properties.Item("ComputerNamePhysicalNetBIOS").Value;
$is_clustered = $properties.Item("IsClustered").Value
if($is_clustered)
{
 Write-Host "The current active node of $cluster_name is $owner_node.";
}
else
{
 Write-Host "$cluster_name is not a clustered instance of SQL Server.";
}

Nice case to work out the SQL version from the product level.

CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'

Bit of PowerShell

PowerShell is becoming more and more useful in our world.

In later weeks, I will go into how I have used powershell to enhance my SQL performance knowledge.

For now a quick script to that will give you information on the processors in your SQL server.  Always handy to know the server guys have given you the box you where promised.

$property = "systemname","maxclockspeed","addressWidth",

            "numberOfCores", "NumberOfLogicalProcessors"

Get-WmiObject -class win32_processor -Property  $property |

Select-Object -Property $property 

Index Space Used

Every need to find out how much disk space an index takes up?:-

SELECT SUM(used_page_count) * 8 AS [Size in kb]

FROM sys.indexes i

JOIN sys.dm_db_partition_stats p ON p.object_id = i.object_id AND i.index_id = p.index_id

WHERE i.object_id=OBJECT_ID('dbo.TableName') AND i.name='Indexname'