SQL Function List:
- String Functions
- Aggregate Functions
String Functions
- Frequently Used String Function
- Less Frequently Used String Functions
- STR Function
- SPACE Function
- CONCAT Function
- ASCII Function
- CHAR Function
- NCHAR Function
- UNICODE Function
- SOUNDEX Function
- DIFFERENCE Function
- PATINDEX Function
- QUOTENAME Function
- STUFF Function
- REPLICATE Function
Aggregate Functions
- Frequently Used Aggregate Function
- Less Frequently
Used Aggregate Functions
- CHECKSUM_AGG
- COUNT_BIG
- STDEV
- STDEVP
- GROUPING
- GROUPING_ID
- VAR
- VARP
Function Name:
RIGHT Function -
returns the specified number of characters from a given input value starting
from the end (right side).
Syntax:
RIGHT(<<Input
Value>>, <<Number of Characters>>)
Arguments:
<<Input
Value>> - Provide input value (string, field name or expression) that
will be used as your input.
<<Number
of Characters>> Provide number of characters that you would like to
receive from the <<Input Value>> you provided.
Simple Example:
SELECT RIGHT('ABC-123-BCD', 3)
Result = 'BCD'
Full Article:
SQL Right Function -
Article that describes the RIGHT function in details and provides examples.
Function Name:
LEFT Function - returns
the specified number of characters from a given input value starting from the
very beginning (left side)
Syntax:
LEFT(<<Input
Value>>, <<Number of Characters>>)
Arguments:
<<Input
Value>> - Provide input value (string, field name or expression) that
will be used as your input.
<<Number
of Characters>> Provide number of characters that you would like to
receive from the <<Input Value>> you provided.
Simple Example:
SELECT LEFT('ABC-123-BCD', 3)
Result = 'ABC'
Full Article:
SQL LEFT Function -
Article that describes the LEFT function in details and provides examples.
Function Name:
SUBSTRING
Function - returns portion of the specified input value based on
starting position and number of characters to be returned.
Syntax:
SUBSTRING(<<Input
Value>>, <<Starting Position>>, <<Number of
Characters>>)
Arguments:
<<Input
Value>> - Provide input value (field name or expression) that will be
used as your input.
<<Starting
Position>> - Provide starting position which indicates where the
portion of input value will be extract from.
<<Number
of Characters>> - Provide number of characters that should be extracts
(from Starting Position).
Simple Example:
SELECT SUBSTRING ('ABC-123-BCD',3,5)
Result
= 'C-123'
Full Article:
Function Name:
LOWER
Function - returns a value after converting specified input value
characters to lower case.
Syntax:
LOWER (<<Input
Value>>)
Arguments:
<<Input
Value>> - Provide input value (string, field name or expression) that
will be used as your input.
Simple Example:
SELECT
LOWER ('Abcd')
Result =
'abcd'
Full Article:
Function Name:
UPPER
Function - returns a value after converting specified input value
characters to upper case.
Syntax:
UPPER
(<<Input Value>>)
Arguments:
<<Input
Value>> - Provide input value (field name or expression) that will be
used as your input.
Simple Example:
SELECT UPPER ('abcd')
Result =
'ABCD'
Full Article:
Function Name:
RTRIM Function
- returns a value after removing any spaces from the right side of the
specified input value.
Syntax:
RTRIM (<<Input
Value>>)
Arguments:
<<Input
Value>> - Provide input value (string, field name or expression) that
will be used as your input.
Simple Example:
SELECT RTRIM (' abc
')
Result =
' abc'
Full Article:
Function Name:
LTRIM Function
- returns a value after removed any spaces from the left side of the
specified input value.
Syntax:
LTRIM
(<<Input Value>>)
Arguments:
<<Input
Value>> - Provide input value (field name or expression) that will be
used as your input.
Simple Example:
SELECT LTRIM (' abc ')
Result =
'abc '
Full Article:
Function Name:
LEN
Function - returns the number of characters of the specified input value.
Syntax:
LEN
(<<Input Value>>)
Arguments:
<<Input
Value>> - Provide input value (field name or expression) that will be
used as your input.
Simple Example:
SELECT LEN ('abc')
Result =
3
Full Article:
Function Name:
CHARINDEX
Function - Is used to find position of a character(s) in a string
(field).
Syntax:
CHARINDEX
(<<Value to Find>>, <<Input Value >>)
or
CHARINDEX (<<Value
to Find>>, <<Input Value >>,<<Search Starting
Position>>)
Arguments:
<<Value to
Find>> - Provide value that you want to find in <<Input
Value>>.
<<Input
Value>> - Provide input value (field name or expression) that will be
used as your input.
Optional:
<<Search
Starting Position>> - Search of <<value to find>> starts
from the beginning of <<input value>> however you can provide
third argument that will specify starting position of the search.
Simple Example:
SELECT CHARINDEX ('@', 'katie@katieandemil.co.uk' )
Result = 6
SELECT CHARINDEX ('@',
'katie@katieandemil.co.uk',6 )
Result = 1
Full Article:
Function Name:
REVERSE
Function - Is used to return the reverse order of a string value.
Syntax:
REVERSE
(<<Input Value>>)
Arguments:
<<Input
Value>> - Provide string value (field name or expression) that will be
used as your input.
Simple Example:
SELECT REVERSE
('abc')
Result = 'cba'
Full Article:
Function Name:
REPLACE
Function - Is used to find value and replace it with specified replacement
value.
Syntax:
REPLACE (
<<Input Value>> ,<< ValueToFind>>,
<<ReplacementValue>> )
Arguments:
<<Input
Value>> - Provide string value (field name or expression) that will be used
as your input.
<<ValueToFind>>
-Provide substring to be found.
<<ReplacementValue>>
-Provide string to replace.
Simple Example:
SELECT Replace('Katie
& Emil', '&', 'and')
Result = 'Katie And Emil'
Full Article:
Function Name:
COUNT
Function - Is used to count rows in a table.
Syntax:
COUNT (
<<Input Value>> )
Arguments:
<<Input
Value>> - Provide input value (field name or expression) that will be
used as your input.
Simple Example:
SELECT COUNT(*)
FROM
[dbo].[DimCustomer]
Result
= 18484
Full Article:
SQL COUNT
Function - Article that describe the COUNT function in details.
COUNT
Function Microsoft Documentation
Function Name:
SUM
Function - returns the total sum of a numeric column.
Syntax:
SUM (
<<Input Value>> )
Arguments:
<<Input
Value>> - Provide expression value, numeric or approximate value that will be used as your input.
Simple Example:
SELECT Sum(SalesAmount) As TotalSalesAmount
FROM
[dbo].[FactInternetSales]
Result
= 29358677.2207
Full Article:
SQL SUM
Function - Article that describe the SUM function in details.
SUM
Function Microsoft Documentation
Function Name:
AVG
Function - returns the average value of a numeric column.
Syntax:
AVG (
<<Input Value>> )
Arguments:
<<Input
Value>> - Provide expression value, numeric or approximate value that will be used as your input.
Simple Example:
SELECT Avg(SalesAmount) As AverageSalesAmount
FROM
[dbo].[FactInternetSales]
Full Article:
SQL AVG
Function - Article that describe the AVG function in details.
AVG
Function Microsoft Documentation
MIN Function
Function Name:
MIN Function
- returns the Minimum value of a numeric column.
Syntax:
MIN (
<<Input Value>> )
Arguments:
<<Input
Value>> - Provide expression value, numeric or approximate value that will be used as your input.
Simple Example:
SELECT
MIN(SalesAmount)
As MinSalesAmount
FROM
[dbo].[FactInternetSales]
Result = 2.29
Full Article:
SQL MIN
Function - Article that describe the MIN function in details.
MIN
Function Microsoft Documentation
Function Name:
MAX Function
- returns the Maximum value of a numeric column.
Syntax:
MAX (
<<Input Value>> )
Arguments:
<<Input
Value>> - Provide expression value, numeric or approximate value that will be used as your input.
Simple Example:
SELECT
MAX(SalesAmount) As
MinSalesAmount
FROM
[dbo].[FactInternetSales]
Result = 3578.27
Article
Version: SQL RTRIM Function
In this step by step SQL Tutorial I will
show you how to use RTRIM function. For the purpose of this
tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL
Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server
2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server
2005 as well
Who is it for?
- People who would like to learn basics of using
functions.
- Those who are preparing for Microsoft
certifications and need to cover this topic.
Contents
Function Name:
RTRIM Function -
returns a value after removing any spaces from the right side of the
specified input value.
Syntax:
RTRIM
(<<Input Value>>)
Arguments:
<<Input
Value>> - Provide input value (string, field name or expression) that
will be used as your input.
In this example I will show you how to use
RTRIM to remove empty space from the right side of the string (input
value). In first column you can see string with space at the
beginning and at the end. The second column
StringWithNoSpaceOnTheLeft you can see that i use function RTRIM to
remove the space from the end of the string (input value).
Below is result. You can see that space was
only removed from the right side.
SQL LEN Function
Function Name:
LEN Function -
returns the number of characters of the specified string value.
Syntax:
LEN
(<<String Value>>)
Arguments:
<<String
Value>> - Provide string value (field name or expression) that will be
used as your input.
In this example I will use
AdventureWorksDW2012 database dbo.DimCustomer table and check how long is the
email address.
Below is example of using LEN and you can
see the results of LEN function using EmailAddress field.
The LEN () by default removes spaces from
the right side but do not remove space from the left side.
In the first example the length of string
is 5 and we get result 5.
In this example we have space at the
beginning of the string and the results give us six as a length of string.
In the last example we have a space
at the end of string and the length of string is 6 but len
function ignores spaces at the end so the result is 5 not 6.
Recently I noticed that someone visited
this page by typing can sql len function detect
space and we have already provided the answer above so LEN ignores
spaces at the end so it cannot detect it but what if you want to count the
space the end?
One
possibility is to use DATALENGTH function that returns 'size' of the string
so if one character is 1byte than example below will give us 6 and it does
take into account space at the end as it does take 1 byte.
SELECT DATALENGTH('kasia ')
NOTE:
but be careful because this function returns size in bytes so if I
provide unicode sting with N'kasia ' than each character is 2 bytes so result
is 12 not 6.
SELECT DATALENGTH(N'kasia ')
Function Name:
LEFT Function - returns
the specified number of characters from a given string starting from the very
beginning (left side)
Syntax:
LEFT(<<String
Value>>, <<Number of Characters>>)
Arguments:
<<String
Value>> - Provide string value (field name or expression) that will be
used as your input.
<<Number
of Characters>> Provide number of characters that you would like to
receive from the <<String Value>> you provided.
Simple Example:
SELECT LEFT('ABC-123-BCD', 3)
Result = 'ABC'
Microsoft Documentation:
In this example I will
use AdventureWorks2012 database dbo.DimCustomer table.
In this example I will show you how to use
LEFT to return first two characters string of PostalCode.
Function Name:
RIGHT Function -
used to take specified number of characters from a string starting from the
end (right side).
Syntax:
RIGHT(<<String
Value>>, <<Number of Characters>>)
Arguments:
<<String
Value>> - Provide string value (field name or expression) that will be
used as your input.
<<Number
of Characters>> Provide number of characters that you would like to
receive from the <<String Value>> you provided.
In this example I will
use AdventureWorks2012 database dbo.DimCustomer table.
In this example I will show you how to use
Right to return a piece of character string of email address. In first column
you can see EmailAddress and the second column TopLevelDomain (last part
of address like .com) you can see that i use function Right() to extract
'.com' from EmailAddress column by using 4 last characters.
NOTE: In results below this function works however for
non .com address this may produce incorrect results and genereally right is
combined with other SQL Functions.
Function Name:
LOWER
Function - returns a string value after converting specified string
value characters to lower case.
Syntax:
LOWER
(<<String Value>>)
Arguments:
<<String
Value>> - Provide string value (field name or expression) that will be
used as your input.
Simple Example:
SELECT LOWER ('Abcd')
Result =
'abcd'
Microsoft Documentation:
In this example I will
use AdventureWorks2012 database dbo.Person.Person table.
In this example I will show you how to use LOWER function to change first
letter in a string to lower case.In first column you can see LastName and the
second column LastNameLowerLetter you can see that last name starts with
lower case.
Function Name:
UPPER
Function - returns a string value after converting specified string
value characters to upper case.
Syntax:
UPPER
(<<String Value>>)
Arguments:
<<String
Value>> - Provide string value (field name or expression) that will be
used as your input.
Simple Example:
SELECT UPPER ('abcd')
Result =
'ABCD'
Microsoft Documentation:
In this example I will
use AdventureWorks2012 database dbo.Person.Person table.
In this example I will show you how to use UPPER function to change letters
in a string to upper case. In first column you can see LastName and
the second column LastNameUpperLetter you can see that last name is
return into upper case.
Returns the reverse order of a string value. For instance word
'cat' reversed becomes 'tac'.
Function Name:
REVERSE Function
- Is used to return the reverse order of a string value.
Syntax:
REVERSE
(<<Input Value>>)
Arguments:
<<Input
Value>> - Provide string value (field name or expression) that will be
used as your input.
In this example I use REVERSE() function to
reverse FirstName field.
Function Name:
SUBSTRING Function -
returns portion of the specified string value based on starting
position and number of characters to be returned.
Syntax:
SUBSTRING(<<String
Value>>, <<Starting Position>>, <<Number of
Characters>>)
Arguments:
<<String
Value>> - Provide string value (field name or expression) that will be
used as your input.
<<Starting
Position>> - Provide starting position which indicates where the
portion of string will be extract from.
<<Number
of Characters>> - Provide number of characters that should be extracts
(from Starting Position).
In this example I will
use AdventureWorks2012 database Sales.Customer table.
In this example I will show you how to use SUBSTRING function
to return a piece of character string of AccountNumber. In first column
you can see AccountNumber and the second
column AccountNumberMiddle7Character you can see that i use
function SUBSTRING to extract ewerything what starts from second
character in value and rund through next severn letters in
the string.
We can use CHARINDEX() function when we
need to find position of a character(s) in a string (field).
Function Name:
CHARINDEX
Function - Is used to find position of a character(s) in a string (field).
Syntax:
CHARINDEX
(<<ValueToFind>>, <<InputValue>>)
CHARINDEX (<<ValueToFind>>, <<InputValue
>>,<<StartingPosition>>)
Arguments:
<<ValueToFind>>
- Provide string value that contains the sequence to be found. String value
which we want to find is limited to 8000 characters.
<<InputValue>>
- Provide string value (field name or expression) that will be used as your
input.
<<StartingPosition>> - String position where the
search for <<StringToFind>> should start.
In the example below I will show you how to
find '@' in EmailAddress. In this example I omit starting position argument
(3rd argument) so this function will start searching for the
specified character from the very beginning.
See below T-SQL code and results.
In the second example I want to find '@' in
the EmailAddress field and the starting position is 7 (let say first
part of email address must be minimum 6 characters). So the first seven
characters are omitted during search for '@' character and the result are
below.
In first email address we get 0 as @ does
not exist AFTER 6th (starting 7th) character.
NOTE: Although we start search
at 7th character the result of CHARINDEX is number of characters from the
very beginning (not 7th position).
Function Name:
REPLACE
Function - Is used to find value and replace it with specified replacement
value.
Syntax:
REPLACE (
<<Input Value>> ,<< ValueToFind>>,
<<ReplacementValue>> )
Arguments:
<<Input
Value>> - Provide string value (field name or expression) that will be
used as your input.
<<ValueToFind>>
-Provide substring to be found.
<<ReplacementValue>>
-Provide string to replace.
In this example I will show you how to use
Replace function to. In first picture you can see that Katie&Emil company
name contains '&' between names. I would like to replace '&' with
'And' you can see in second picture how I did it.
SQL Soundex
What is soundex function?
SQL Server Soundex is a function that
converts string (words) into 4 characters code. The code represent
"sound" of the word so you can use function on another word and see
if they "sounds" the same. If 4 characters code is the same
than it sounds the same.
Let's show you a simple SQL query example:
SELECT SOUNDEX('Katie') --returns K300
SELECT SOUNDEX('Emil') --returns E540
As you can
see Katie & Emil don't sound the same. Let's give you another example.
SELECT SOUNDEX('Katy') --returns K300
SELECT SOUNDEX('Katie') --returns K300
Katie & Katy sound similar so they have
the same code K300. Actually this is nice example because when Katie
introduces herself and than people type the name on a computer they sometimes
type Katy instead of Katie because both names sound similar.
What is the purpose of Soundex function?
You can use SQL Soundex function to compare
two words and find out if they sound the same. Remember we only check sounds,
that means we check sound similarity but that doesn't mean
the words are similar or the same. Katie & Katy sound similar BUT there
are two different names so they are distinct which is important when you want
to clean data. You don't want to change Katie to Katy because Katie is Katie
;)
When to use SQL Server Soundex function?
I must admit I don't often use Soundex
function for data cleansing purposes myself as sound similarity is usually
insufficient for most tasks I do; but there are exceptions when I could use
this function. This function can be used when you have a list of values which
give you unique sound code, so when you compare another word against he
list you can find one match only (or no match).
SQL Soundex Example
Recently I have come across this question
on Microsoft forum SQL Question and soundex as proposed solution the problem was that month is mispelled and
contains odd numbers. The task was to get proper month out of the fields.
Here are some example of incorrect input.
Row 1: January300
Row 2: January398
Row 3: January
Row 4: February40
Row 5: February20694
Row 6: Januuary 5454
One of the solution is to build a list of
incorrect values and map it to correct month name which can be time
consuming, another one is too take out number and spaces but it doesn't deal
with mispelled months (row 6). So can we use soundex? Let's try it.
First let's see if we have unique soundex
code for all 12 months:
SELECT SOUNDEX('January') as [Soundex month code] union
all
SELECT SOUNDEX('February') union all
SELECT SOUNDEX('March') union all
SELECT SOUNDEX('April') union all
SELECT SOUNDEX('May') union all
SELECT SOUNDEX('June') union all
SELECT SOUNDEX('July') union all
SELECT SOUNDEX('August') union all
SELECT SOUNDEX('September') union all
SELECT SOUNDEX('October') union all
SELECT SOUNDEX('November') union all
SELECT SOUNDEX('December')
and the results are below. Left side is
January to December, Right side is sorted by soundex code to see if we have
the same soundex month codes.
This looks good. So let's try to tidy up
our list of incorrect values.
SQL Soundex data cleansing
Here is the code that we use to perform
data cleansing using t-sql soundex function.
select s.id, s.DirtyMonth,
case
when soundex('january') = soundex(s.DirtyMonth)
then 'January'
when soundex('feburary') = soundex(s.DirtyMonth)
then 'February'
-- and
so on
else 'unknown'
end as CleanMonthName
from (
Select 1 as ID, 'January300' as DirtyMonth Union
All
Select 2, 'January398' Union All
Select 3, 'January' Union All
Select 4, 'February40' Union All
Select 5, 'February20694'Union
All
Select 6, 'Januuary 5454') as s
Below are results of the above SQL query:
Success!! That worked fine.
IMPORTANT: Soundex worked fine in our case
BUT be careful when you use it because it might not be so accurate with
different examples of inaccurate data so ensure you test it on full set of
invalid values. In our case anything that doesn't match month goes to 'unknown'
so some kind of logic or process should be put in place to tidy up the
exception.
SQL Soundex final words:
Soundex is not commonly used but sometimes
it can help especially when we to do something quickly. Data cleansing in
data warehouses project is generally done using fuzzy lookup and in SQL
Server 2012 we have Master Data Services (master data management) and Data
Quality Services which can help us with this kind of tasks.
If you want to use T-SQL for data cleansing
check Beyond SoundEx - Functions for Fuzzy Searching in MS SQL
Server
Function Name:
SUM
Function - returns the total sum of a numeric column.
Syntax:
SUM (
<<Input Value>> )
Arguments:
<<Input
Value>> - Provide expression value, numeric or approximate value that will be used as your input.
In this example I will
use AdventureWorks2012 database dbo.FactInternetSales table.
In this example I will show you how to use
Sum function to return a TotalSalesAmount.
Function Name:
COUNT
Function - Is used to count rows in a table or count rows based using
specific logic.
Syntax:
COUNT
(<<Input Field>>)
Arguments:
<<Input
Field>> - Provide field name that will be used as your input.
In this example I will use
AdventureWorksDW2012 database, dbo.DimCustomer table and show you
how to use COUNT aggregate function. In my select I put COUNT and inside the
brackets I type asterisk (*) after the asterisk type new column name.
COUNT(*) counts all rows in result set we
get (which can be limited by WHERE clause)
Function Name:
MIN Function
- Returns the lowest available value based on the given set of rows.
Syntax:
MIN (
<<Input Field>> )
Arguments:
<<Input Field>> - Provide Field name that will be used to
perform the MIN function logic.
In this example I will
use AdventureWorks2012 database dbo.FactInternetSales table and
show you how to use Min to display the smallest sales amount.
You can see below that I used MIN function
and provided SalesAmount field so my result is the smallest Sales Amount
value.
SQL MAX Aggregate Function Introduction
Function Name:
MAX Function
- Returns the highest available value based on the given set of rows.
Syntax:
MAX (
<<Input Field>> )
Arguments:
<<Input Field>> - Provide Field name that will be used to
perform the MAX function logic.
SQL MAX Aggregate Function Example
In this example I will
use AdventureWorks2012 database dbo.FactInternetSales table
and show you how to use Max to show the biggest sales amount.
Below I used MAX function and used
SalesAmount field so my result show the biggest Sales Amount.
Function Name:
AVG
Function - returns the total sum of a numeric column.
Syntax:
AVG (
<<Input Value>> )
Arguments:
<<Input
Value>> - Provide expression value, numeric or approximate value that will be used as your input.
In this example I will
use AdventureWorksDW2012 database, dbo.FactInternetSales table.
In this example I will show you how to use
AVG aggregate function. In a select I put Avg and inside the brackets I type
star after the star I type new column name.
T SQL
DBA
I Open Object Explorer and find Databases folder
I click on Databses folder, and from drop
down box I select New Databses.
In the New Database window I type Database
name in this case Test and click OK.
I go back to the Databases folder and click
Refresh.
Now my Database Test is visible.
Below I will show you how to create new
database using script in SSMS 2012.
I open Object Explorer,AdventureWorksDW2012
database and open new query.
In the new query I type CREATE DATABASE
DatabaseName statement.
I click Execute to run the query
In the Messages box I get information that
my query was run successfully.
I go back to the Database folder to
refreshed it.
Now in the Object Explorer my new database
Test is visible.
sql server 2012 restore database
SQL Server 2012 Editions: This tutorial is created using SQL Server 2012
Business Intelligence Edition but the steps should be the same in Enterprise
edition and although we haven't tried it they should be the same (maybe with
fewer options) in Express edition and standard edition of SQL Server 2012.
SQL Server Versions: Please be aware that you can restore 2012 backup
files and you should be able to restore previous versions (althought haven't
tried yet) but what you cannot do is to use previous version of SQL for
instance SQL Server 2008 and try to restore backup file that was created
using SQL Server 2012.
SQL Server 2012 Restore Backup file
Open object explorer in the toolbar. Click
connect icon. You will be presented with "connect to server" pop
up. Ensure Server Type drop down box has "Database Engine"
selected. Type server name whichin my case is localhost and I use shortcut
dot "." Click Connect button.
Now we can go to Databases folder and right
click on it, from drop down box select Restore Database.
]
Restore Database window should appear. I am
in General section, In Source section I selected Device radio
button and clicked on the icon with three dots.
In the Select backup devices, click Add
button
and now select Backup file location
and file actual backup file. By default it should point
to the correct path in most cases.
NOTE: If you cannot see the .bak files
it might be because they don't have .bak extension on your file. In such
situations change bottom right drop down box from backup files
to all files.
After you select the file; the file name
appears in the File name text box at the bottom. Click OK
Once we selected backup file we can see the
path in the Backup media section. Click OK
In SQL Server 2012 after you click ok and
certain fields are pre-populated. Please be aware that this new behaviour and
you will most likely not see in previous version for example SQL Server 2008.
Source // Database drop down box shows
available databases in the backup file.
Destination // Database drop down box shows
database available in the server that you are connected to. You can type any
database name and it will create it.
NOTE: When you click Database drop down box
in destination section. You will notice that the database name will
automatically change and you won't be able to change it to original value
using drop down box but you can still type it. Hopefully Microsoft will
change this behaviour as it is not very intuitive.
Backup sets to restore section contains all available backups. We have
only full but sometimes you can have different backup types with multiple
lines (we will try to write article and post link here. Remember to use
search bar that will check hundreds of our articles).
Click ok
KNOWN ISSUES: If you don't see OK button; it might be because
your screen resolution it too low. This issue seems to be more frequent with
virtual machines that are usually on lower screen resolutions. To fix it try
to increase the screen resolution.
After we click Ok we get message box which
in our case says "Database 'database name' restored
successfully'"
NOTE: When you perform the database restore
and the database already exists than it cannot be in use, otherwise restore
operation may fail.
NOTE: You should see restored database
under databases folder however if you use previous version of SQL Server you
might have to right click databases folder and click refresh.
sql server move database to a different drive
Before we began let's clarify why I am
doing this. In my case I have virtual drive that has 30GB drive and I am
running low on disk space. My biggest files on C drive are SQL Server
databases therefore I have decided to move them to F drive that has much more
disk space.
NOTE: This tutorial is applicable to certain development
machines and should not be used on production servers as usually you require
extra steps that involve "moving" folder permissions and all
dependand processes.
See below images that shows that I have
877MB left on my C drive and 17.7GB available on F Drive.
My current database files (mdf = data &
ldf = log file) are located in C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA which is default installation path
and below you can see the biggest 3 databases that I will want to move from C
drive to F drive. As you can see below my 3 biggest databases will give me
extra 1GB free space on C drive.
How to move databases to a different drive
There are diffent ways to move a database
from one drive to another one like backup / restore, T-SQL script but in our
case we want the easiest and quickest one so we will use DETACH database
method using SSMS and simply copy/paste files and ATTACH them back to the
database instance.
I opened SSMS, connected to my local
instance of SQL Server right clicked one of the databases and selected TASKS
and then clicked Detach. See below example.
I got a detach database dialog box where I
clicked ok.
NOTE: when you detach database you might
noticed that the database does NOT disappear in solution explorer. This is
because SSMS doesn't refresh the database list so you might click top level
databases folder and click refresh and the detached database should disappear
from the list.
I have repeated DETACH operation for the
remaining 2 database and now I'm ready to move my database files to new
location which in my case is F:\Database\Data
See below example that shows files in
current C drive location and new location. Notice that one database has 2
kind of files mdf (=data) and ldf (=log file) so I will move all 6 files.
I cut & pasted the files and below you
can see that files in new F drive location.
Now that we have files in new location it
is time to ATTACH the database to server. To do that connect to your
instance. Right click database folder and click attach.
We are presented with attach database
dialog box. I pressed Add button. Find my new F drive location with new
files. Selected one database and clicked OK
After clickng ok the dialog box was filled
in with information. See example below.
I have repeated the same ATTACH steps for
the remaning files and below is screenshot of the disk space after I moved
the database files to new drive.
sql server shrink log file
Usage: As a developer I often work with development
environements and is it common task for me to either shrink a log after I
perform database restore from live environement or just reduce my current log
file. The common problem is that shrink command is not always shrinking the
log file and and this tutorial I will cover this particular issue as well:
NOTE: This method is used to permanently remove log data
which is useful in development environment but it is not recommended in
production environments.
See below an example of a database with log
file (ldf) that is slightly over 200MB.
In order to successfuly reduce the size of
a log using shrink method we will have to make a change to database recovery
model. To reduce log file to almost "zero" MB we need to
change recovery model to SIMPLE. If you don't do that you might have an issue
of shrink database not shrinking.
I opened SSMS and connected to my local
instance. I right clicked my database and selected properties.
In Database properties I went to options
page (on the left side) and you can see on the right side recovery model that
in my case is set to FULL and which I will change to SIMPLE.
See below example of my change. After the
change I clicked ok.
It's time to shrink our database. I right
click the database go to Shrink and select files. See example below.
A dialog box appeared and I changed File
type to Log, select reorganize pages before releasing unsed space and shrink
file to 0 mb.
NOTE: Remember full shrink does not work when you are
NOT in simple recovery mode so you might be left with much larger log file
than 0 mb.
Below you can see that my ldf which is log
file was reduced to "zero" mb more precisely 1mb (1024KB).
NOTE: Do you need script? Remember you can
use SCRIPT option at the top of the dialog box to get SQL code.
sql server 2012 add user login with sql server authentication
n this step by step SQL Tutorial I will
show you how to Add a new user login to SQL Server with SQL Server
Authentication mode. For the purpose of this tutorial I will use SQL Server
2012.
Prior knowledge: You should have some
knowledge about security and difference between windows authentication vs SQL
Server Authentication. We plan on writing blog posts about it and once we
have it we will provide links in this tutorial. (If we forget use the search
bar at the top).
INTERNAL REVIEW: Katie please add a
screenshot which shows that SQL SERVER is in Mixed Mode Authentication
(create separate article) and add link here.
Once SSMS is open I go to Object Explorer,
Security, Logins folder.
NOTE: There are two kinds of Security
folders. Database level Security folder that exists in specific database
under Databases Folder that we won't be using and Server Level Security
folder that is located directly below "Databases" folder (see
below).
To Add new user login right click Logins folder
and click New Login...
In the Login window I select SQL Server
authentication.
I type Login name and password and press
OK.
I get error message saying "Password
validation failed. The password does not meet Windows policy requirements
because it is not complex enough".
NOTE: Password should be strong
and there are a number of conditions you need to meet. One of them might be
the need to use special characters like $. Avoid using generic passwords
like Pa$$w0rd that unfortunatelly is used way too often! but when you don't
have password management software or processes that often seems to be easiest
option for some people...... until something bad happens ;). This practice is
sometimes forgivable if you use it locally or on development box and the
database is not exposed externally and in our case we will use
"weak" password.
To create "weak" password and
avoid error message I untick Enforce password policy and click ok
NOTE: Very often you will set some kind of
permissions under Server Roles (data reader is popular), User Mapping or
Securables. Once we write more tutorial we will provide links with more
information (if we forget use the search bar).
Now my new login is added and visible in
the Logins folder.
INTERNAL REVIEW: Katie please add
screenshot how you can use the new login to log in.
You can now try to connect to the server
using SQL Server Authentication method and give it to users that require
remote connection.
Problems with logging in are often related
error 18456 for more information visit SQL Server Error 18456
sql server add linked server
Note to myself.... Sometimes for
development and testing purposes there is a need to add linked server using
SQL Server that will actually point to itself so queries work properly with
linked server name.
Below is the quote that I always forget:
EXEC sp_addlinkedserver
@server=N'[LinkedServerName]',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'.';
SQL Server compressed backup tool
MS SQL Compressed Backup tool
Below is code that allows to perform restore of two compressed backups (same
database) and moves it to new location:
msbp.exe restore "local(path=Z:\DatabaseName.bak.gz;path=Z:\DatabaseName2.bak.gz;)"
"gzip()"
"db(database=DatabaseName;MOVE='DatabaseName'TO'C:\DatabaseName.mdf';MOVE='DatabaseName_log'TO'C:\DatabaseName.ldf';)"
sql case sensitive string comparison
In SQL Server when you compare strings
sometimes 'A' equals 'a' and sometimes 'A' does not equal 'a'. This depends
on collation setting. Collation decides how strings are compared.
you can have many different collations but
the most important factor is if collation you have is case sensitive or case
insensitive. If one of the strings or fields is case sensitive than 'A' is
not the same as 'a' but if both are case insensitive then 'A' equal 'a'.
Below are several examples explaining case
sensitivity strings comparisons and results.
select CASE WHEN 'a'='a' THEN 'match' else 'doesn''t match' end
The result of the above sql is always
'match'
select CASE WHEN 'A'='a' THEN 'match' else 'doesn''t match' end
The result of the above sql is 'match' if
database collation set to case insensitive and 'doesn't match if collation is
case sensitive.
select CASE WHEN 'A' collate
sql_latin1_General_CP1_cs_as ='a' THEN 'match' else 'doesn''t
match' end
If this case we force case sensitive collation
using collate command and the results of the above query is always 'doesn't
match'
select CASE WHEN lower('A') collate
sql_latin1_General_CP1_cs_as =
lower('a') THEN 'match' else 'doesn''t match'
end
To compare two strings and ensure that are
case insensitive I often use lower function on both sides. In the example
above I enforced case sensitivity using collate command but I used lower on
both sides hence made is case insensitive so sql result is 'match'
You could use collate instead of lower
function but I personally prefer lower function as it is easier to read.
NOTE: If you use different collation you
may suffer performance issues as query is not able to use index seek (based
on my current knowledge).
Bitmask in Sql Server - Explained
In this tutorial I will explain what
is a bitmask and how it can be used in SQL Server. For the
purpose of this tutorial I will columns_updated (MSDN) function
that can be used inside a trigger to detect if field was updated or not.
Purpose of BitMask
Before we delve into details let's discuss
the purpose of BitMask. Imagine a situation where you have a number of values
(or fields) and each of them is either true or false. For instance you can
have: IsDelivered, IsShipped, IsProduced, IsTested. Values stored in these
fields are very simple and are either true or false. BitMark is used to
"combine" all of them into a single value and in order to do that
in "encodes" the values which means you have to "decode"
it to read it.
Why would you do that? It is less readable!
Yes but and it saves space and is more efficient and this is the main reason
why you would use it.
Another example I will give is a SQL Server
trigger. Imagine a situation where you need to perform a certain action IF a
certain field or fields have been updated. In order to do that you need to
"detect" which fields have been updated. To do that you could check
every single column (using update() function)
however for applications that support high volume operations this might not
be efficient and therefore doing a single comparison would be much more
effective and this is where BitMask can help as it can provide you a
single value that tells you which fields have been updated.
BitMask basics
To understand BitMask let's start with
explaining the term BIT. BIT is a unit of information that can hold value
either 0 or 1 (that's how computers work!). SQL Server will use stored bits
in bytes. 1 byte = 8 bits below we have 8 bits with values:
|
1 bit
|
2 bit
|
3 bit
|
4 bit
|
5 bit
|
6 bit
|
7 bit
|
8 bit
|
|
0
|
0
|
1
|
0
|
1
|
0
|
0
|
1
|
|
1
|
0
|
0
|
1
|
1
|
0
|
1
|
0
|
So what does it mean? It means that in
first example 3rd, 5th and 8th value is true and the rest is false. Can you
"decode" last row?
Let's go back to our example with detect
which field is updated:
|
1 bit
|
2 bit
|
3 bit
|
4 bit
|
5 bit
|
6 bit
|
7 bit
|
8 bit
|
|
IsProduced
|
IsPaid
|
IsSent
|
IsCancelled
|
IsDelivered
|
IsReturned
|
IsRepaired
|
IsSatisfied
|
|
1
|
1
|
1
|
0
|
1
|
0
|
1
|
0
|
Can you see what 11101010 means, based on
the above table? (Note 1 = True and 0 = false)
BitMask Encoding
11101010 can be stored in field of
varbinary datatype but additionally we need to assign a unique numeric
value which will also be used to generate a combination which SUM result is
always unique.
Below is a table that is used generate
varbinary result and numbers that I assigned.
Fields with value false are always -
00000000
Field 1 if true - 00000001 = 1
Field 2 if true - 00000010 = 2
Field 3 if true - 00000100 = 4
Field 4 if true - 00001000 = 8
Field 5 if true - 00010000 = 16
Field 6 if true - 00100000 = 32
Field 7 if true - 01000000 = 64
Field 8 if true - 10000000 = 128
To better understand it let's take our
previous result which was 11101010 which I will reverse in accordance to the
above table 01010111.
so 01010111 = 1 + 2 + 4 + 16 + 64 = 87
87 is an important result as it can be ONLY
created by the following combination of numbers 1 + 2 + 4 + 16 + 64
If SQL Server Columns_Updated() function
returns 87 that means that field 1, 2, 3, 5, and 7 was updated.
What if I have more than 8 values?
In SQL Server 8 bits (values) = 1 Byte. If
you need more than 8 values you can add extract bytes and when you extract
data from varbinary field you can use substring function. First
"character" is 1 byte (8 bits) and second character is field 9 to
16 and so on.
SQL Server Compressed backup without enterprise edition
Compressed backups (+ page, row
compression) is part of sql server enterprise edition. To perform compressed
backups (gzip) that saves a lot of space as it allows restore from gzip
directly use the link below (non enterprise editions of sql server).
SQL Server Compressed Backup
Documentation
Getting Started Examples
Below are some examples to get you started using msbp.exe on the command
line. msbp.exe must run on the same machine as SQL Server, due to how SQL
Server dumps the data to msbp.exe.
Basic Backup
To backup to a standard SQL Server *.bak file, run the following
command:
msbp.exe backup "db(database=model)"
"local(path=c:\model.bak)"
Basic Restore
To restore from a standard SQL Server *.bak file, run the following
command:
msbp.exe restore "local(path=c:\model.bak)"
"db(database=model)"
Compressed Backup
Using the basic command above, you can add any number of plugins between
the source and destination. For example, you may want to compress the data
with the gzip plugin:
msbp.exe backup "db(database=model)"
"gzip()" "local(path=c:\model.bak.gz)"
Compressed Restore
And to restore compressed the file, insert "gzip()" in the
middle again. Here the gzip plugin knows it is restoring the database, so
it will uncompress the data:
msbp.exe restore "local(path=c:\model.bak.gz)"
"gzip()" "db(database=model)"
Multithreaded Compressed Backup
Using the compression command above, you can add any number of files.
Since each file runs on its own thread, you can acheive multithreaded
compression. For example:
msbp.exe backup "db(database=model)"
"gzip()"
"local(path=c:\model1.bak.gz;path=c:\model2.bak.gz;)"
T-SQL read registry
Sometimes it is useful to read registry using t-sql from 'regedit' for instance to build dynamic
values and SQL Server allows you to do that. See below an example
EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 'MSSQLServer'
The extra step is to insert the values into variable and below I used temp
table to do that
declare @regValue as nvarchar(250)
DROP TABLE #temp_table
CREATE TABLE #temp_table (Value nvarchar(500), Data nvarchar(500))
INSERT INTO #temp_table EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 'MSSQLServer'
SELECT @regValue = Data FROM #temp_table
select @regValue as MyData
This example was created using SQL Server 2008 R2 and hopefully also works
in 2008 and 2005
Microsoft sql server error 18456 login failed for user
When connecting to Microsoft SQL Server
and trying to use usually SQL Authentication method you may get event ID error
18456 login failed for user you provided. See below example.
This usually means that your connection request was successfully received
by the server name you specified but the server is not able to grant you
access for a number of reasons and throws error: 18456. This eventID
sometimes provides also state 1 which actually isn’t that useful as due to
security reasons any error is converted to state 1 unless you can check
logging on the server. Microsoft does not provide very useful message boxes
so below are some explanations why you get the error.
Below is a list of reasons and some brief explanation what to do:
SQL Authentication not enabled: If you
use SQL Login for the first time on SQL Server instance than very often
error 18456 occurs because Windows Authentication (only) is set in Server
properties (security section).
To Access Server Properties, Open
SSMS, go to Object Explorer pane (use view if you can't see it). Use
the connect button to connect to database engine for your server. Once
connected you will see it in object explorer. Right click server and click
properties. Server Properties window will appear.
See below screenshot that might be
causing SQL login to fail
You should set Server Authentication to SQL Server Windows
Authentication Mode (see below). Once you select SQL Server
Authentication you have to restart the server.
Restart required: You have to restart the service when you change
server authentication but bear in mind if someone hasn't restart the
service this might be the reason why you get 18456. Remember service
restart should be carefully planned on production servers as it will clear
certain information (cache) and may impact performance of the server.
NOTE: I recently tried to restart service using SSMS
2012 on Windows 7 and it didn't work.... actually the message below was
hidden behind SSMS window and I just did not see it so ensure you get this
message when you decide to restart the service.
Invalid userID: SQL Server is not able to find the
specified UserID on the server you are trying to get. The most common cause
is that this userID hasn’t been granted access on the server but this could
be also a simple typo or you accidentally are trying to connect to
different server (Typical if you use more than one server)
Invalid password: Wrong password or just a
typo. Remember that this username can have different passwords on different
servers.
less common errors: The userID might be
disabled on the server. Windows login was provided for SQL Authentication
(change to Windows Authentication. If you use SSMS you might have to run as
different user to use this option). Password might have expired and
probably several other reasons…. If you know of any other ones let me know.
18456 state 1 explanations:
Usually Microsoft SQL Server will give you error state 1 which actually
does not mean anything apart from that you have 18456 error. State 1 is
used to hide actual state in order to protect the system, which to me makes
sense. Below is a list with all different states and for more information
about retrieving accurate states visit Understanding "login
failed" (Error 18456) error messages in SQL Server 2005
|
ERROR STATE
|
ERROR DESCRIPTION
|
|
State 2 and State 5
|
Invalid userid
|
|
State 6
|
Attempt to use a Windows login name with SQL
Authentication
|
|
State 7
|
Login disabled and password mismatch
|
|
State 8
|
Password mismatch
|
|
State 9
|
Invalid password
|
|
State 11 and State 12
|
Valid login but server access failure
|
|
State 13
|
SQL Server service paused
|
|
State 18
|
Change password required
|
SQL Server date and datetime error
What is the issue?
Try the following two statements.
SELECT CAST('2012-09-04' AS
DATETIME)
SELECT CAST('2012-09-04' AS
DATE)
I would personally expect that they would
produce the same date. In this case it is 2012 September 08 so I would
expect SQL Server to convert the string to dates in exactly the same way.
The result will actually depend on the
Language your Login uses or you can set the language in current connection
which I will use to demonstrate the issue.
See below the same statement one executed
using English another one executed using BRITISH language.
SET
LANGUAGE 'ENGLISH'
SELECT CAST('2012-09-04' AS
DATETIME)
SELECT CAST('2012-09-04' AS
DATE)
SET
LANGUAGE 'BRITISH'
SELECT CAST('2012-09-04' AS
DATETIME)
SELECT CAST('2012-09-04' AS
DATE)
I would expect to get consistent results
and 2012 September 04th.
and the results are:
Notice the red line. For language British
and CAST as DATETIME we got 2012 April 09th!
I classify it as a bug!
So I provided exactly the same input
string but for BRITISH (and some other European languages) behavior is
different for CAST AS DATE vs CAST AS DATETIME. I classify
it as a bug because there is no way we should get different date just
because we cast it to DATETIME and not DATE. Obviously
it might be difficult to fix it by Microsoft as it would potentially
have negative impact on existing systems but I hope some kind of
plan will be made to remove it (introduce new data type?)
What are the risks?
Risks are fairly high as your code
might misbehave for instance if you use BRITISH and you use WHERE DateField
= '2012-09-04' then that would actually return 2012 April 09! So depending
on Login language you might get different result which means on Development
box it might work. Code might have been tested and everything is ok and on
Production it goes terrible wrong! just because login language is
different.
Date bug workaround
- Use YYYYMMDD instead of YYYY-MM-DD - That
will actually work and is best practice when you provide hard coded
values BUT in practice might be very difficult to monitor and ensure
everyone uses this format.
- Make Login language consistent on each
environment. This method ensure that you get exactly the same behavior
so you can spot an error during testing on development environment
(not only in production!) and you can do that by using Policy
Management in SQL Server which I will describe in next article!
A severe error occurred on the current command
Hi
My colleague Rob, come across recently a very odd sql server bug which gave
him the following message
A severe error occurred on the current command. The results, if any,
should be discarded.
He passed it to me and we managed to narrowed it down to this sql
statement:
SELECT cast('test' as nvarchar(10)) as MyColumn
INTERSECT
select 'test'
ORDER BY MyColumn;
The error occurs in SQL Server 2008 and SQL Server 2008 R2 build
10.50.1617.0 and it was already on Microsoft connect page raised as bug A severe error occurred on the current command.
The results, if any, should be discarded.
It seems this was fixed in SQL 11 which has not been released yet and
potentially in the latest version of SQL Server 2008 R2. I'm going to
install all updates to my R2 and see if I still get the severe error
message and I will try it on SQL Server 2011 CTP3 as well (I will update
the blog soon).
Solution? Do not use cast in first query or use it in both cases or change
the order! See below change of order which does work!
SELECT cast('test' as nvarchar(10)) as MyColumn
INTERSECT
select 'test'
ORDER BY MyColumn;
|
|
|
