SQL Server Collations Find current collation and all supported collations

Have you ever needed to check your database or SQL instance to find out its collation? Have you ever wanted to check whether a collation is supported by Windows or SQL Server?

Well I am going to show you how to find out the information quickly and easily.

The below query will show the instance level collation.
SELECT SERVERPROPERTY(‘collation’)

1

The query below will show the collation setting at the database level

 

SELECT DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘Collation’) SQLCollation

2

Sometimes its important to know the collations which the OS supports and the following query will help list all the collations which are supported by the OS.

 

SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE ‘SQL%’;

3

When doing migrations it is very important to ensure that the Collation setting is maintained and to know whether it is supported by the SQL instance. The below query will help you easily and quickly ascertain that information.

 

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE ‘SQL%’;

4

 

Hopefully that covers all your collation gathering requirements but if you do have another technique which I have missed then please leave them in the comments below.

 

Generate random numbers in PowerShell with Get-Random

Generate random numbers in PowerShell with Get-Random

Have you ever wanted to generate random numbers for passwords, test data…. and wondered how you could easily generate them?

Well I was confronted by the same situation on one of our servers where I needed some random numbers for inputting wait times to test an application but didn’t have my usual tool of choice ( MS Excel ) available to me.

I remembered the Get-Random command from memory but used the Get-Help command to allow me to use the correct syntax.

Executing Get-Random will produce a random number.

get-random
get-random

 

This was quite a good start but then I needed only 4 digit figures and I then had to use the minimum and maximum parameters.

Get-Random -Maximum 9999 -Minimum 1000

2

The above command solved that problem for me and I think it reflects how easily readable the PowerShell language is.

The last scenario I had was producing random numbers for some temporary passwords. I looked at the help for Get-Command and noticed the inputobject and count parameters which I thought would fulfil my need.

Get-Random -InputObject (10000..99999) -Count 3

3

The above command shows the range of values which I gave and the number of random numbers I needed in the Count parameter.

Hopefully the above will help you with your random number needs. Please add your feedback or other solutions in the comments below.

 

ASCII String function in SQL Server

Have you wondered what the ASCII value of a character is whilst using SQL Server? Or needed to quickly check the ASCII value? Or wondered how the ASCII Function worked?

Well the other day I saw one of the developers using the ASCII function which I was not familiar with so I decided to a bit of exploring.
SELECT ASCII( ‘A ‘)
SELECT ASCII( ‘B ‘)
SELECT ASCII( ‘C ‘)
SELECT ASCII( ‘D ‘)
SELECT ASCII( ‘E ‘)
SELECT ASCII( ‘F ‘)
1
The above query shows how easy it is to return ASCII values.
2
The second query shows how you can get the ASCII values from values within a column.
I hope that you liked the above post and hope that you can tell me about your adventures with the ASCII function.

 

List Hotfixes installed with PowerShell with Get-Hotfix

Have you ever wondered what updates have been applied to your Windows operating system?

Well a quick and easy way to do that is to use the PowerShell command Get-Hotfix. This will list out the description, the HotfixID and when it was installed.

I used this on a few PCs where it appeared that updates were not being applied and it showed me when the last updates were installed and hence helped me identify the PCs which were having the trouble and disconnect them from the network.

1

Statistical Aggregate functions in SQL Server

Have you ever wondered how to do program or perform simple statistical function within SQL Server? Well the kind people at Microsoft have enabled us to all to save time by giving us a few inbuilt functions which save us from having to solve them programmatically.
I have used the unit Price column in the SalesOrderDetail table within the Adventureworks database to illustrate the in built functions

 

SELECT --UnitPrice,
COUNT(UnitPrice) AS 'Count of UnitPrice',
AVG(UnitPrice) AS 'Average UnitPrice',
MIN(UnitPrice) AS 'Minimum UnitPrice',
MAX(UnitPrice) AS 'Maximum UnitPrice',
SUM(UnitPrice) AS 'Total UnitPrice',
VAR(UnitPrice) AS 'Variance of UnitPrice',
STDEV(UnitPrice) AS 'Standard deviation of UnitPrice'
FROM [Sales].[SalesOrderDetail] 

1
The COUNT function counts the number of values within the column.
The AVG function returns the average unit price column.
The MIN function returns the smallest value within the column.
The MAX function returns the largest value within the column.
The SUM function returns the sum total of all the values within the column.
The VAR function returns the variance of all the values within the column.
The STDEV function returns the standard of all the values within the column.

 

I am sure that I have probably missed out a few functions so please let me know via the comments below.

SQL Cursor to Kill all connections to a database

Have you ever tried to restore over a database but found that all attempts are being blocked by an annoying SPID? Or had hundreds of orphaned SPIDs running crazy on your instance?

Well I have the solution for you with the below script which uses a cursor to kill all connections to a database.

DECLARE @spid varchar(10)</pre>
DECLARE kill_spid CURSOR fast_forward FOR
SELECT SPID FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'AdventureWorks2012' AND spid > 50
OPEN kill_spid
FETCH NEXT FROM kill_spid INTO @spid
while @@FETCH_STATUS = 0
BEGIN EXEC ('Kill ' + @Spid)
FETCH NEXT
FROM kill_spid INTO @spid
END
CLOSE kill_spid
DEALLOCATE kill_spid

Make sure that you change AdventureWorks2012 for your database name and double check that you have the correct database name as I have seen it when people put the wrong database name in and its never a pretty sight.

 

SQL Cursors

Cursors are a way of manipulating data and interacting with them one at a time. They have a bad reputation within the SQL world as they go against the SET based logic and they can have a very high performance cost. Where possible you should ask yourself whether you could avoid using a Cursor. This is because of the performance advantages a SET based solution has and that Cursor problems only increase when the tasks are scaled up.

The five general steps of a cursor are:

  1. Declaration of the cursor
  2. Opening the cursor
  3. Fetching and manipulating the data
  4. Closing the cursor
  5. Deallocating the cursor

A simple example of a Cursor is below.

Declare @Databases varchar(50)</pre>
Declare DatabasesOnIntance CURSOR READ_ONLY FOR SELECT name FROM sys.databases order by name
Open DatabasesOnIntance
Fetch next from DatabasesOnIntance into @Databases
While @@FETCH_STATUS = 0
Begin
Print @Databases
Fetch next from DatabasesOnIntance into @Databases
End
Close DatabasesOnIntance
Deallocate DatabasesOnIntance
Cursor Results
Cursor Results

As you can see the Cursor I have created has printed each database on the instance

WHILE, BREAK, and CONTINUE Statements

Have you ever wondered how to create a loop in SQL? Or wondered how to break and escape a loop?

Well I am going to briefly introduce you to the WHILE, BREAK, and CONTINUE Statements which will satisfy your curiosity

The first command I will introduce you to is WHILE

DECLARE @i int = 1;
WHILE @i < = 5

BEGIN

PRINT @i;

SET @i = @i + 1;

END

Loop

Loop

As you can see the WHILE statement will force the loop to continue until we reach 5. This is very useful if you need to batch process tasks and limit the amount done within each batch.

The next command to learn is CONTINUE. This command forces you to go back to the beginning of the loop.

 

DECLARE @i int = 1;
WHILE @i < = 5

BEGIN

PRINT @i;

SET @i = @i + 1;

CONTINUE; -- This will cause the WHILE to loop back

PRINT 'You wont see this due to the CONTINUE commands cleverness.';

END
Loop
Loop

Once the WHILE command is satisfied the CONTINUE command will allow the loop to complete.

The final command related to loops is the BREAK command.

 

DECLARE @i int = 1;
WHILE @i < = 5

BEGIN

PRINT @i;

SET @i = @i + 1;

BREAK; -- Force the WHILE loop to terminate

PRINT 'You wont see this due to the BREAK commands cleverness..';

END

 

Loop
Loop

As you can see from the example when the BREAK is encountered the loop is broken and it only ever prints 1.

In the real world many developers try not to use the BREAK and CONTINUE commands as they can be easily avoided in code and many people find it makes code less readable and unnecessarily complex.

 

 

Learn about the WAITFOR TIME & WAITFOR DELAY

Have you ever wanted to pause a command for a short period or wanted to run a transaction at a specific time.

 

Well I am going to quickly show you how to do them both using the WAITFOR command.

SELECT GETDATE()
WAITFOR DELAY '00:00:10';
BEGIN
SELECT GETDATE()
END;
Date time
Date time

The above code shows you how to delay two print current date and time commands by ten seconds. It can be easily modified for any time such as 43 minutes, 43 hours….

SELECT GETDATE()
WAITFOR TIME '12:22:00';

BEGIN

SELECT GETDATE()

END;
Date time
Date time

The above command will cause the transaction to wait until that time before executing the command. The time can be modified at your pleasure for whatever time you require.

The two processes do carry a processor overhead as the transaction will be running until it’s completed. Also, these commands can usually be replaced by an appropriately timed SQL Agent job which reduces the process overhead and makes the administration of it far easier.

Powershell Alias List

Have you ever wandered whether you could speed up your script writing,shorten down your snippets or come across strange commands in PowerShell?

Well PowerShell’s Aliases would be the answer to all the above scenarios. I first came across them when I saw it in a friends script and could not ascertain what GDR was and is later transpired to be GET-PSDRIVE.

Though I don’t use them in my own scripts as it makes it harder to read, debug and modify. I do believe everyone should familiarise themselves with the alias commands and have listed them below.

Alias Command
% ForEach-Object
? Where-Object
ac Add-Content
asnp Add-PSSnapin
cat Get-Content
cd Set-Location
chdir Set-Location
clc Clear-Content
clear Clear-Host
clhy Clear-History
cli Clear-Item
clp Clear-ItemProperty
cls Clear-Host
clv Clear-Variable
cnsn Connect-PSSession
compare Compare-Object
copy Copy-Item
cp Copy-Item
cpi Copy-Item
cpp Copy-ItemProperty
curl Invoke-WebRequest
cvpa Convert-Path
dbp Disable-PSBreakpoint
del Remove-Item
diff Compare-Object
dir Get-ChildItem
dnsn Disconnect-PSSession
ebp Enable-PSBreakpoint
echo Write-Output
epal Export-Alias
epcsv Export-Csv
epsn Export-PSSession
erase Remove-Item
etsn Enter-PSSession
exsn Exit-PSSession
fc Format-Custom
fl Format-List
foreach ForEach-Object
ft Format-Table
fw Format-Wide
gal Get-Alias
gbp Get-PSBreakpoint
gc Get-Content
gci Get-ChildItem
gcm Get-Command
gcs Get-PSCallStack
gdr Get-PSDrive
ghy Get-History
gi Get-Item
gjb Get-Job
gl Get-Location
gm Get-Member
gmo Get-Module
gp Get-ItemProperty
gps Get-Process
group Group-Object
gsn Get-PSSession
gsnp Get-PSSnapin
gsv Get-Service
gu Get-Unique
gv Get-Variable
gwmi Get-WmiObject
h Get-History
history Get-History
icm Invoke-Command
iex Invoke-Expression
ihy Invoke-History
ii Invoke-Item
ipal Import-Alias
ipcsv Import-Csv
ipmo Import-Module
ipsn Import-PSSession
irm Invoke-RestMethod
ise powershell_ise.exe
iwmi Invoke-WmiMethod
iwr Invoke-WebRequest
kill Stop-Process
lp Out-Printer
ls Get-ChildItem
man help
md mkdir
measure Measure-Object
mi Move-Item
mount New-PSDrive
move Move-Item
mp Move-ItemProperty
mv Move-Item
nal New-Alias
ndr New-PSDrive
ni New-Item
nmo New-Module
npssc New-PSSessionConfigurationFile
nsn New-PSSession
nv New-Variable
ogv Out-GridView
oh Out-Host
popd Pop-Location
ps Get-Process
pushd Push-Location
pwd Get-Location
r Invoke-History
rbp Remove-PSBreakpoint
rcjb Receive-Job
rcsn Receive-PSSession
rd Remove-Item
rdr Remove-PSDrive
ren Rename-Item
ri Remove-Item
rjb Remove-Job
rm Remove-Item
rmdir Remove-Item
rmo Remove-Module
rni Rename-Item
rnp Rename-ItemProperty
rp Remove-ItemProperty
rsn Remove-PSSession
rsnp Remove-PSSnapin
rujb Resume-Job
rv Remove-Variable
rvpa Resolve-Path
rwmi Remove-WmiObject
sajb Start-Job
sal Set-Alias
saps Start-Process
sasv Start-Service
sbp Set-PSBreakpoint
sc Set-Content
select Select-Object
set Set-Variable
shcm Show-Command
si Set-Item
sl Set-Location
sleep Start-Sleep
sls Select-String
sort Sort-Object
sp Set-ItemProperty
spjb Stop-Job
spps Stop-Process
spsv Stop-Service
start Start-Process
sujb Suspend-Job
sv Set-Variable
swmi Set-WmiInstance
tee Tee-Object
trcm Trace-Command
type Get-Content
wget Invoke-WebRequest
where Where-Object
wjb Wait-Job
write Write-Output