April 15th, 2010
Using OpenQueries you can execute a pass-through query to a linked server within SQL Server. The Syntax for an open query is relatively simple:
Select * from openquery({SERVER_NAME}, ‘SELECT {FIELD} FROM {TABLE}’)
This can be useful however OpenQueries dont accept parameters. The way round this is to put the parameters outside the string. For example:
SELECT QUERY.* FROM OPENQUERY({SERVER_NAME}, ‘SELECT {TABLE}.{ROW} AS {R1} FROM {TABLE}’) AS QUERY<br />
WHERE QUERY.{R1} = {@Variable}
Note: Its best practice to use aliases for the openquery.
Posted in Workarounds | No Comments »
April 15th, 2010
I often get asked the difference between NVarchar and Varchar. Both datatypes are used to store character information. While they are largely identical there is one significant difference. NVarchar is used to store unicode data which means it can store multilingual data in your database tables. This means that Nvarchar can take up twice as much space as varchar, therefore unless you plan to store extended character codes Varchar will offer the best performance.
Posted in Query Optimisation | No Comments »
April 15th, 2010
A quick and easy way to list all tables in a database is:
SELECT * FROM information_schema.tables
Posted in Database Structure | No Comments »
April 15th, 2010
Often there is a requirement to Capitalize the inital Letter of Each Word in a String. The following function does it quickly and easily.
CREATE FUNCTION [dbo].[FN_INITIALCAPS] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000) AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ‘ ‘
ELSE SUBSTRING(@InputString, @Index – 1, 1)
END
IF @PrevChar IN (‘ ‘, ‘;’, ‘:’, ‘!’, ‘?’, ‘,’, ‘.’, ‘_’, ‘-’, ‘/’, ‘&’, ””, ‘(‘)
BEGIN
IF @PrevChar != ”” OR UPPER(@Char) != ‘S’
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
END
SET @Index = @Index + 1
END
RETURN @OutputString
END
Tags: Strings
Posted in Strings | No Comments »
April 15th, 2010
You can show parameters in a report with the following code:
=Parameters!Centre.Value
However if you have multivaluded parameters this will produce an error on the report (#Error).
To solve this you can use the following code to show parameters:
=Join(Parameters!Centre.Value, “,”)
Tags: Error, PARAMETERS, SSRS
Posted in SSRS | No Comments »
April 15th, 2010
It is sometimes necessary to split a string before or after a specified character. For instance if you are trying to strip the username out of a name/domain field you can use the following code.
SELECT SUBSTRING(SYSTEM_USER, CHARINDEX(‘\’, SYSTEM_USER) + 1, LEN(SYSTEM_USER))
It Will return username given domain\username.
The code can then be extended to work around any character specified. For instance if you have a name field which is [firstname] [lastname] you can split it into seperate firstname and lastname fields.
SUBSTRING([Field], 1, CHARINDEX(‘ ‘, [Field]) – 1)SUBSTRING([Field], CHARINDEX(‘ ‘, [Field]) + 1, LEN([Field]))
Tags: Charindex, String Manipulation, Substring
Posted in Strings | No Comments »
April 15th, 2010
Unique Indexes and Primary Keys have similar properties. They can both be used to enforce foreign keys, both be used as bases for computed columns they can both be used as clustered and non clustered indexes and they can both be declared on multiple columns.
However they are not the same thing and should not be treated or even thought of as such. There are numerous differences. Primary Key Columns cannot contain nulls whereas the columns that make up a unique index can*. You are only allowed a single primary key but as many unique indexes as you need. A unique index is thus an alternate key.
* SQL Server allows a single null in a unique index. Oracle however allows several as nulls by thier very nature are unknown its up for debate if the Oracle method breaks unique rules or not.
Posted in Beginning SQL Server | No Comments »
April 15th, 2010
A colleague recieved the following error when attempting to display a query execution plan:
SHOWPLAN permission denied in database.
A quick internet search revealed that SHOWPLAN is a database level permission which allows users to view query execution plans. It is automatically assigned to the db_owner database role and to the sysadmin fixed server role. If you don’t want to assign the user to either of these roles you can just grant SHOWPLAN permissions to the role by running the following SQL:
USE {Database}
GO
GRANT SHOWPLAN TO {UserName}
GO
Tags: Error, Query Execution Plan
Posted in Errors | No Comments »
April 15th, 2010
Indexes can improve performance on your database however they do need to be maintained to. As more data is added to tables the corresponding data is added to an index somtimes in an inefficient order, this process is known as fragmentation.
To target fragmentation you first have to work out which indexes need optimising. I do this with the following code:
SELECT ID.object_id,
ID.index_id,
I.name AS Index_Name,
ID.Avg_Fragmentation_In_Percent AS Fragmentation,
T.name AS Table_Name
FROM sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS ID
JOIN sys.indexes AS I WITH(NOLOCK)
ON ID.Object_id = I.Object_id
AND ID.Index_id = I.Index_id
JOIN sys.objects AS T
ON T.object_id = I.object_id
WHERE Avg_Fragmentation_In_Percent >= 10
AND I.name IS NOT NULL
It ouputs all the indexes with fragmentation over 10% showing the index name, the table its associated with as well as the fragmentation in percent. Any fragmentation below 10% can be left alone.
For the remainder you can choose to rebuild or reorganize an index. A loose rule of thumb is to rebuild an index if its over 30% fragmented and reorganize if its over 10% but below 30%.
The syntax to reorganize or rebuild an index is relatively simple:
ALTER INDEX {INDEX_NAME} ON {TABLE_NAME}
REORGANIZE
Or
ALTER INDEX {INDEX_NAME} ON {TABLE_NAME}
REBUILD
Tags: Indexes
Posted in Database Optimisation | No Comments »
April 15th, 2010
Duplicates are a problem for any database. Luckily the problem can be easily solved using a combination of CTEs and Windowed Functions. Firstly let’s create a table and insert our duplicates:
Create Table Employees(ID int NOT NULL, Forename varchar(50), Surname varchar(50)) Insert into Employees values(1,’John’,'Doe’)
Insert into Employees values(1,’John’,'Doe’)
Insert into Employees values(2,’John’,'Smith’)
Insert into Employees values(2,’John’,'Smith’)
Insert into Employees values(3,’Jane’,'Smith’)
Insert into Employees values(4,’Jane’,'Doe’)
We can now build our code to remove the duplicate John Doe and John Smith:
With Duplicates As
( Select ID,
Forename,
Surname,
ROW_NUMBER() OVER (PARTITION BY ID,Forename,Surname Order By ID) AS Ranking
From Employees
) Delete
From Duplicates
Where Ranking >1
If we run a Select statement:
SELECT * FROM Employees
We can see that the duplicate records have been removed.
Tags: Duplicates
Posted in Database Optimisation | No Comments »