robert

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.

SQL Server Kill All Connections

by
Facebooktwitterredditpinterestlinkedinmail

Killing all connections in SQL Server is something that you may need to do from time to time. The two times that I’ve needed to do this most is when I’m restoring a database in my test environment or when I need to rename a database. Both of these operations require that there are not any open connections to the SQL Server database. There are a couple good scripts that I will show here. My favored method is to set it in single user mode.

 
Kill All Connections Using Single User Mode
This is the easiest way kill all the connections to the database. It basically sets the database to only allow 1 user (you) and it will kill all the other connections.

-- Kill all connections and set to single user mode
USE	MASTER
GO
ALTER	DATABASE [DatabaseName] 
SET 	SINGLE_USER
WITH	ROLLBACK IMMEDIATE
GO

-- Do your operations

-- Set the database back in to multiple user mode
USE	MASTER
GO
ALTER	DATABASE [DatabaseName] 
SET 	MULTI_USER
GO

 
Kill All Connections Using The Kill Command
The following query will loop through all the open processes on the database and kill each one. Note that this may take a little bit of time to execute if there are long running transactions, it may take some time to roll them back.

USE	MASTER
GO

DECLARE	@Spid INT
DECLARE	@ExecSQL VARCHAR(255)

DECLARE	KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT	DISTINCT SPID
FROM	MASTER..SysProcesses
WHERE	DBID = DB_ID('DatabaseName')

OPEN	KillCursor

-- Grab the first SPID
FETCH	NEXT
FROM	KillCursor
INTO	@Spid

WHILE	@@FETCH_STATUS = 0
	BEGIN
		SET		@ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50))

		EXEC	(@ExecSQL)

		-- Pull the next SPID
        FETCH	NEXT 
		FROM	KillCursor 
		INTO	@Spid  
	END

CLOSE	KillCursor

DEALLOCATE	KillCursor

 
Reference: https://msdn.microsoft.com/en-us/library/bb522682(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms173730.aspx

Script Multiple Objects [Easy]

by
Facebooktwitterredditpinterestlinkedinmail

Have you ever noticed that you can only highlight 1 object in the Object Explorer in SQL Server Management Studio? This usually is enough for most things you do, but if you want to script multiple objects at once… it becomes a pain. Well there is a way to script multiple objects at once, and it’s super easy.

  1. Open the Object Explorer Details screen by selecting View -> Object Explorer Details.
    Script Multiple Objects 1
  2. Highlight the folder with the objects that you would like script. A list of all objects in that folder will appear on the right. This works for tables, stored procedures, etc…
    Script Multiple Objects 2
  3. Highlight all the objects that you want, right click, and select Create To.
    Script Multiple Objects 3