selecting

How To Create A Date Table

by
Facebooktwitterredditpinterestlinkedinmail

Many times when developing SQL Server databases you have the requirement to show all dates in a range. One way to do this is with a date table. One of the most common reasons that I use a date table is when I have a reconciliation report. In a reconciliation report I want to show all of the days in a date range, even if they don’t have data on those days. SQL Server doesn’t have a function to generate dates in a range. So to accomplish this, I create a date table using the following script.

CREATE
TABLE	DateTable
		(DateValue	DATETIME)

GO

DECLARE @StartDate		DATETIME = '01/01/2014'
DECLARE @NumberOfDays	INT = 3000

INSERT
INTO	DateTable
		(DateValue)
SELECT	DATEADD(DAY, RowNum, @StartDate) AS DateValue
FROM	(SELECT	TOP (@NumberOfDays)
				p1.PARAMETER_ID,
				ROW_NUMBER() OVER (ORDER BY p1.PARAMETER_ID) - 1 AS RowNum
		 FROM	sys.all_parameters p1
		 CROSS	JOIN sys.all_parameters p2) NumberTable

 

What Is SQL Server

by
Facebooktwitterredditpinterestlinkedinmail

What Is SQL Server
SQL Server is a database management system (DBMS) produced by Microsoft. The purpose of SQL Server is to store and retrieve data for use in computer applications. These applications could be mobile apps, web sites, desktop applications, or more.

Data in SQL Server is stored in tables. These tables have rows and columns, much like you would see in an Excel spreadsheet. SQL Server accesses this data using a coding language called T-SQL (Transact-SQL). This code is called a query. Here is a sample query that pulls all of the data from a table.

SELECT	*
FROM	AnimalTable

 
Easiest Definition Of “What Is SQL Server”
SQL Server is a program built by Microsoft that other programs can use to store and retrieve data.

SQL Server Synonyms – What They Are And How To Use Them

by
Facebooktwitterredditpinterestlinkedinmail

What Is A Synonym?

A synonym in SQL Server can be thought of like having another name for a table.  The table doesn’t even have to be in your own database.  The intent of having synonyms is for an easy way to reference objects in another database.  It basically gives you a short alias to call server objects that live in another database.  You can create a synonym for tables, views, stored procedures, and functions!

 
Example Without Synonym:

SELECT	*
FROM	FarmAnimals.dbo.SoundsOfAnimals

 
Example With Synonym:

SELECT	*
FROM	AnimalSounds

In the example above, the synonym AnimalSounds represents FarmAnimals.dbo.SoundsOfAnimals.

 
NOTE: Because the synonym is basically an alias for a different server object, INSERT, UPDATE, SELECT, DELETE operations happen directly on the base table and not on the synonym.

 
Why Would You Use A Synonym?

There are 2 main reasons for using synonyms.  The first reason is to make the code a little easier to read.  It makes all of the remote objects look like local objects.  The second reason is that it creates a layer of abstraction.  If the base table changes, then you only need to change the synonym.  If you did not use a synonym, then you would need to go in to every stored procedure, view, function, etc… and change the reference to the new table.

 
Real-Life Use

So… although it is a neat concept, I really don’t use synonyms very much.  The one application where I see it very useful is when you do not own the database that you are referencing (like if you bought a 3rd party product).  Product updates could change the table/stored procedure names… if you use a synonym to reference the database tables, then it would only take 1 change on your side to get your program working again.

 
 
Reference:  http://msdn.microsoft.com/en-us/library/ms187552.aspx

 

Order By On A Union?

by
Facebooktwitterredditpinterestlinkedinmail

Have you ever tried to add an ORDER BY clause to a UNION operator and have gotten this error message?

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

UNION

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

So what the heck is this and how do we fix it?

 
What The Error Says
The error basically says that you have an issue with your syntax. The ORDER BY clause should only be applied one time in your query. Logically SQL Server will pull the data requested from the two tables… then it will perform the sort operation on the data.

When people see the UNION operator, they think that it is combining two different result sets, thus they need two ORDER BY clauses. In fact, you only need to order the final result set. This is done by placing the ORDER BY command at the very end of the statement.

SELECT AnimalName
FROM Animal
-- Notice the removed ORDER BY here?

UNION

SELECT AnimalName
FROM Animal
ORDER BY AnimalName

 

Parse XML With SQL Server

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server has a lot of useful functionality built-in when it comes to XML.  Today I want to show you an easy way to parse XML data in to a usable format inside your query.

 
For this example, I will be parsing XML data into a temp table, then inserting that data in to a table.

DECLARE	@XMLToParse  XML

-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored proc
SET	@XMLToParse =	'<Animals>
			   <LandAnimals>
				<Animal>Baboon</Animal>
				<Animal>Yak</Animal>
				<Animal>Zebra</Animal>
			   </LandAnimals>
			</Animals>'

-- Declare temp table to parse data into
DECLARE	@ParsingTable  TABLE
	(Animal	VARCHAR(100))

-- Parse the XML in to the temp table declared above
INSERT
INTO	@ParsingTable
	(Animal)
SELECT	xmlData.A.value('.', 'VARCHAR(100)') AS Animal
FROM	@XMLToParse.nodes('Animals/LandAnimals/Animal') xmlData(A)

-- Insert into the actual table from the temp table

INSERT
INTO	Animals
	(AnimalName)
SELECT	Animal
FROM	@ParsingTable

 
The statement that parses the XML has 2 parts to it.

FROM   @XMLToParse.nodes('Animals/LandAnimals/Animal') xmlData(A)

This part says to parse the @XMLToParse variable (previously filled with the XML file) using the method built in to SQL Server called .nodes.  It specifies the repeating node to be Animals/LandAnimals/Animal.  It assigns an aliases this XML parsed records as a table named xmlData.  The (A) is the column name of the rowset.  This will be referenced in the select part of the statement.

 

SELECT  xmlData.A.value('.', 'VARCHAR(100)') AS Animal

The select part of this statement references xmlData (which is the table aliases) and A (which is the column named for that table).  It calls the .value function to return the value from the table/column.  For the .value function, you pass in 2 elements.

The first element is the field.  In this case we are passing in just a period.  We do this because there is no node below the Animal node.  If there was, we would need to specify it here..

The second parameter is the datatype that you would like the value to be casted as.  Then I always aliases the field to something relevant.

 
Although parsing XML can be a little confusing in SQL Server, it is very powerful.  This is a great way to pass bulk data to a stored procedure from any type of client application.