selecting

SQL Server Data Types To JSON Data Types

by
Facebooktwitterredditpinterestlinkedinmail

In SQL Server 2016 they are added support for outputting your query results in JSON. When you serialize your data to JSON using the FOR JSON clause, a data type conversion happens. The data types are converted from SQL Server data types to JSON supported data types.

For more information on how to convert your result set to JSON: www.sqlserver.info/syntax/json-in-sql-server/

Here is a quick reference table showing the data type conversions that are happening:

[table width=”500″ colwidth=”250|250″ colalign=”left|left”]
SQL Data Type,JSON Data Type
VARCHAR/NVARCHAR,String
CHAR/NCHAR,String
INT,Number
BIGINT,Number
FLOAT,Number
DECIMAL,Number
NUMERIC,Number
BIT,Boolean
DATETIME,String
DATETIME2,String
DATE,String
TIME,String
DATETIMEOFFSET,String
VARBINARY,Base64-encoded string
BINARY,Base64-encoded string
IMAGE,Base64-encoded string
TIMESTAMP,Base64-encoded string
ROWVERSION,Base64-encoded string
UNIQUEIIDENTIFIER,String
MONEY,String
[/table]

 

SQL Server Nested Select

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server nested select is also known as a “sub query”. This is where instead of using a list, table, or view, you actually use another query. In this article, we will go over the basics on how to use a SQL Server nested select statement.

Basic Rules For A Nested Select
Here are a couple basic rules when using a nested select statement in SQL Server.

  • The nested select statement must be enclosed in parentheses.
  • When using an ORDER BY clause in your nested select, you must specify TOP command.
  • Many times, you will need to alias the nested select.

 
Sample Nested Select
Here is a sample where we return the basic animal information with their average weight.

SELECT	A.AnimalID,
		A.AnimalName,
		W. AverageWeight
FROM	Animal A
INNER	JOIN (SELECT	AnimalID,
				  		AVG(AnimalWeight) AS AverageWeight
	          FROM		AnimalWeights
	          GROUP		BY AnimalID) W ON W.AnimalID = A.AnimalID

In the above example, we do our normal inner join, but we join to a nested select statement. This allows you to join on the results from the nested select. You can see that the nested select statement will get the average weight of the animals by AnimalID. That whole query is inside parenthesis. Then we alias that nested select results as “W”. We can then reference the 2 return values (AnimalID and AverageWeight) outside of the nested select statement by using the table alias W.

 
 
NOTE: Remember that you will need to alias your nested select statements most of the time. This can stop you up if you aren’t looking for it.

JSON In SQL Server

by
Facebooktwitterredditpinterestlinkedinmail

Finally in SQL Server 2016, Microsoft SQL Server now supports JSON serialization. It provides it through the FOR clause… like XML serialization. This will take a recordset and output it in JSON format with very little effort by the coder.

SELECT	AnimalID,
AnimalName,
Color
FROM	Animal
FOR	JSON PATH

As you can see. To get the output to come in JSON format, all you need to do is add FOR JSON PATH at the end of your query. Simple right? Here is what the results would look like.

[
	{
		"AnimalID":1,
		"AnimalName":"Dog",
		"Color":"Brown"
	},
	{
		"AnimalID":2,
		"AnimalName":"Flamingo",
		"Color":"Pink"
	},
	{
		"AnimalID":3,
		"AnimalName":"Polar Bear",
		"Color":"White"
	}
]

Adding All Columns In A Table To A Query [Easy]

by
Facebooktwitterredditpinterestlinkedinmail

I wanted to share this quick tip with you on how to add a list of all the columns in a table to your query window in SQL Server Management Studio. This is the easiest way I know!

  1. Expand the Object Explorer to show the table that you want to add the columns for.
        Adding All Columns 1
  2. Select the Columns folder on to your query window.
        Adding All Columns 2
  3. Drag the whole folder on to your query window and just drop it where you want it. That’s it!
    Adding All Columns 3