All notes
Mssql

Intro

Pagination

Offset, Limit

SQL Server 2008/R2, 2005

The idea is to use ROW_NUMBER(). SqlAuthority.com.


DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID FROM (
  SELECT SalesOrderDetailID, SalesOrderID, ProductID,
  ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
  FROM Sales.SalesOrderDetail
) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber+1)
GO

NOTE: pageNumber here starts from 0.

SQL Server 2012

Microsoft technet.

OFFSET-FETCH can be used only with the ORDER BY clause. And it is only supported in and after SQL Server 2012.


SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Select


// Get all the database names with tsql
SELECT * FROM sys.databases
go

// Get all table names
use db_name
go
select * from sys.tables

GO

MSDN.

GO [count]

count: a positive integer. The batch preceding GO will execute the specified number of times.

  • A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
  • Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server.
  • Do not use a semicolon as a statement terminator after GO.

What is the difference in semicolon and GO?

The semi-colon is used to signify the end of a statement itself, not necessarily a whole batch.


USE AdventureWorks2012;
GO

-- After "--" comes the comments.

DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch.

-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
GO

SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in batch.
sp_who
GO

-- Yields an error because ; is not permitted after GO
SELECT @@VERSION;
GO;

Bulk insert

MSDN.

Imports a data file into a database table or view in a user-specified format in SQL Server.

Installation

MSDN.

Microsoft Management Console (mmc.exe) uses the SQLServerManagerversion.msc file (such as SQLServerManager13.msc for SQL Server 2016) to open Configuration Manager. Here are the paths to the last four versions when Windows in installed on the C drive.

SQL Server 2016	C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014	C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012	C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2008	C:\Windows\SysWOW64\SQLServerManager10.msc

How to install SQL Server 2012.

Language Reference

Variables

Technet.

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement.


USE AdventureWorks2012;  
GO  
DECLARE @find varchar(30);   
/* Also allowed:   
DECLARE @find varchar(30) = 'Man%';   
*/  
SET @find = 'Man%';   
SELECT p.LastName, p.FirstName, ph.PhoneNumber  
FROM Person.Person AS p   
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID  
WHERE LastName LIKE @find;

Set statements

QUOTED_IDENTIFIER

MSDN.

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks (").

ANSI_NULLS

SO.

When SET ANSI_NULLS is ON:

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard:

CONCAT_NULL_YIELDS_NULL

SO.

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL.

When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

If SET CONCAT_NULL_YIELDS_NULL is not specified, the setting of the CONCAT_NULL_YIELDS_NULL database option applies.

Expressions

MSDN.


/* Simple CASE expression:   */
CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]   /* Run when input_expr == when_expr */
     [ ELSE else_result_expression ]
END

/* Searched CASE expression:  */
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]   /* Run when Bool_expr is true. */
     [ ELSE else_result_expression ]
END

OR is not supported. SO.


CASE ebv.db_no
  WHEN 22978 THEN 'WECS 9500'
  WHEN 23218 THEN 'WECS 9500'
  WHEN 23219 THEN 'WECS 9500'
  ELSE 'WECS 9520'
END as wecs_system

/* Better, use: */

CASE
  WHEN ebv.db_no IN (22978, 23218, 23219) THEN 'WECS 9500'
  ELSE 'WECS 9520'
END as wecs_system

Datatypes

char, varchar, nchar, nvarchar

MSDN: nchar, nvarchar.

MSDN: char, varchar.

nchar and nvarchar are used to store Unicode data and use the UNICODE UCS-2 character set. Thus the storage size is two times n bytes. By comparason, the storage size for char(n) is n bytes.

The ISO synonyms for nchar are "national char", nvarchar - "national char varying".

nvarchar(max) indicates that the maximum storage size is 2^31-1 bytes (2 GB).

sysname is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.

Table

technet.microsoft.com.


USE AdventureWorks2012;
GO

DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO

--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

-- Creating an inline table-valued function
-- The following example returns an inline table-valued function. It returns three columns ProductID, Name and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

USE AdventureWorks2012;
GO

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

-- To invoke the function, run this query.
SELECT * FROM Sales.ufn_SalesByStore (602);

uniqueidentifier

technet.microsoft.com.


-- The following example converts a uniqueidentifier value to a char data type.
DECLARE @myid uniqueidentifier = NEWID();  
SELECT CONVERT(char(255), @myid) AS 'char';  

-- Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length are truncated.
DECLARE @ID nvarchar(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';  
SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;  
-- Here is the result set.
-- String                                       TruncatedValue  
-- -------------------------------------------- ------------------------------------  
-- 0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong    0E984725-C51C-4BF4-9960-E1C80E27ABA0 

What is dbo

SO: table names start with dbo.

dbo is the default schema in SQL Server. Wcf: dbo for database object?

You can create your own schema by browsing to Databases - Your Database - Security - Schemas.

To create one using a script is as easy as (for example):

CREATE SCHEMA [EnterSchemaNameHere] AUTHORIZATION [dbo]

You can use schemas with Entity Framework - even with code first if you like: [Table("Customer", Schema = "MySchema")].

Other

Square brackets

SO: what is the use of the square brackets.

The brackets are required if you use keywords or special chars in the column names or identifiers. wcfNote: it is the escape char.


-- Oh no! Incorrect syntax near the keyword 'user':
create table test ( id int, user varchar(20) )

-- Works fine:
create table test ( id int, [user] varchar(20) )

SELECT *
FROM [My Table]      --Identifier contains a space and uses a reserved keyword.
WHERE [order] = 10   --Identifier is a reserved keyword.

SO.

Indexes

Clustered vs Nonclustered

MSDN: Clustered and Nonclustered Indexes.

An index is used to speed up retrieval of rows from a table or view.

Clustered indexes sort and store the data rows in the table or view based on their key values.

If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Features

Bulk import/export

bcp


bcp [database_name.] schema.{table_name | view_name} | "query"
    {in data_file | out data_file | queryout data_file | format nul}
    [-c] (Performs the operation using a character data type; It uses char as the storage type)
    [-w] (Performs the bulk copy operation using Unicode characters, uses nchar as the storage type)
    [-n] (Performs the bulk-copy operation using the native (database) data types of the data)
    [-N] (Performs the bulk-copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data)
    [-d database_name]
    [-f format_file]
    [-o output_file]
    [-S [server_name[\instance_name]]
    [-t field_term] (field terminator. The default is \t)
    [-T] (connects to SQL Server with a trusted connection using integrated security)
    [-U login_id]
    [-P password]

data_file: The path can have from 1 through 255 characters. The data file can contain a maximum of 2^63 - 1 rows.

format nul: The format option requires the -f option; creating an XML format file, also requires the -x option. You must specify nul as the value (format nul).

Examples:


# Export whole table:
bcp dbName.dbo.tableName out dataFile -w -U sa -P pass -S localhost\SQLEXPRESS

# Export table format:
bcp dbName.dbo.tableName format NUL -f tableFormat.txt -w -U sa -P pass -S localhost\SQLEXPRESS

# Query out:
bcp "select * from dbName.dbo.tableName" queryout dataFile.txt -w -U sa -P pass -S localhost\SQLEXPRESS

Best practices

Use native format (-n) to avoid the separator issue when exporting and importing using SQL Server. Export data from SQL Server using the -c or -w option if the data will be imported to a non-SQL Server database.

Verify data when using BCP OUT. For example, 1 - BCP OUT, 2 - BCP IN, and then 3 - BCP OUT, compare the two outputs from BCP OUT to verify that the data is properly exported.

Builtin Functions

System Functions

@@TRANCOUNT

MSDN: trancount.

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.


PRINT @@TRANCOUNT
-- 0
BEGIN TRAN
    PRINT @@TRANCOUNT
    -- 1
    BEGIN TRAN
        PRINT @@TRANCOUNT
        -- 2
    COMMIT
    PRINT @@TRANCOUNT
    -- 1
COMMIT
PRINT @@TRANCOUNT
-- 0

PRINT @@TRANCOUNT
-- 0
BEGIN TRAN
    PRINT @@TRANCOUNT
    -- 1
    BEGIN TRAN
        PRINT @@TRANCOUNT
        -- 2
ROLLBACK
PRINT @@TRANCOUNT
-- 0

ROW_NUMBER()

MSDN. Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

It's one of a class of windowing functions (the others being Rank, DenseRank and NTile). Top limits the maximum number of rows returned by a query.

ROW_NUMBER ( )
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

value_expression specifies the column by which the result set is partitioned.

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
Here is the result set.

Row FirstName    LastName               SalesYTD
--- -----------  ---------------------- -----------------
1   Linda        Mitchell               4251368.54
2   Jae          Pak                    4116871.22
3   Michael      Blythe                 3763178.17
4   Jillian      Carson                 3189418.36
...

CONVERT

Syntax: CONVERT(data_type(length),data_to_be_converted,style).

Style:


CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)
// Dec 29 2008 11:45 PM
// 12-29-2008
// 29 Dec 08
// 29 Dec 2008 16:25:46.635

SQL Server Management

Where is the server log

Method 1

StackOverflow. In SQL Server 2005, go to tree view on the left and select Server (name of the actual server) -- Management -- Activity Monitor.

Method 2

Microsoft. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

StackOverflow. You can enable connection logging. For SQL Server 2008, you can enable Login Auditing. In SQL Server Management Studio, open SQL Server Properties > Security > Login Auditing select "Both failed and successful logins". Make sure to restart the SQL Server service.

Backup

SO: what is the LDF file.

The LDF stand for 'Log database file' and it is the transaction log. It keeps a record of everything done to the database for rollback purposes, you can restore a database even you lost .msf file because it contain all control information plus transaction information.

Connect Sql server on Linux

Microsoft solution: sqlcmd


# -m-1, output all information.
# -q, cmdline query.
# -Q, cmdline query and exit.
# Print current SQL server version.
sqlcmd -S tcp:ServerIP,ServerPort -d DatabaseName -U Username -P UserPassword -m-1 -Q"select @@version"

For intallation on CentOS, see centos.note.

freetds

TDS (Tabular Data Stream) protocol.

Uninstalling the ODBC Driver on Linux

MSDN.


rm -f /usr/bin/sqlcmd
rm -f /usr/bin/bcp
rm -rf /opt/microsoft/msodbcsql
odbcinst -u -d -n "ODBC Driver 11 for SQL Server"

# TDSVER here override default setting.
TDSVER=7.0 tsql -H serverIP -p serverPort -U domain\\username -P password
TDSVER=7.0 tsql -H serverIP -p serverPort -U username -P password

# Connect to the server by its name in the freetds.conf file.
# Port defaults to 1433.
tsql -S servername -p port -U username -P password

TDS Version

VendorVersionTDS Version
Sybase4.92+5.0
Microsoft6.0, 6.54.2
Microsoft7.0/20007.0

About Sybase: Sybase is an enterprise software and services company that produces software to manage and analyze information in relational databases. Sybase is a standalone subsidiary of SAP. Wikipedia.

FAQ

Can't login on SQL Azure

On serverA I had a username "user1", and on SQL server I had also a username "user1". So if I login with "user1" from serverA to SQL server, the connection failed because it uses "[email protected]" at first.

The solution is to change the login name to "[email protected]".

Cannot connect to 127.0.0.1\sqlexpress

SO: connecting to SQL server express.


REM Fail!!!
sqlcmd -S 127.0.0.1\sqlexpress -d DatabaseName -U Username -P UserPassword -m-1 -Q"select @@version"

REM Success!
sqlcmd -S .\sqlexpress -d DatabaseName -U Username -P UserPassword -m-1 -Q"select @@version"

REM Success!
sqlcmd -S localhost\sqlexpress -d DatabaseName -U Username -P UserPassword -m-1 -Q"select @@version"

Enum type

SO: sql server equivalent to MySQL enum data type.

It hasn't one. There's a vague equivalent:


mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))

The best solution (in MySQL as well) is to create a lookup table with the possible values as a primary key, and create a foreign key to the lookup table.

In non-strict mode with MySQL, an invalid enum can be inserted as a NULL, which allows for a MySQL enum not getting inserted and not failing when the value wasn't specified in the list of values. A foreign key constraint on a lookup table would cause a failure.

What is Transact-SQL

Wikipedia: Transact-SQL.
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL.
Oracle has its PL/SQL.
Both of them are turing complete.

What version of sql server should I install?

MSDN.

LocalDB (SqlLocalDB)
    LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Application and Database Development tools like Visual Studio or embedded with an application that needs local databases.

Express (SQLEXPR)
    Express edition includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely.

Express with Tools (SQLEXPRWT)
    This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server 2014 Management Studio. Choose either LocalDB or Express depending on your needs above.
SQL Server Management Studio Express (SQLManagementStudio)
    This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, full version of SQL Server 2014 Management Studio, etc. Use this if you already have the database and only need the management tools.

Express with Advanced Services (SQLEXPRADV)
    This package contains all the components of SQL Server Express including the full version of SQL Server 2014 Management Studio. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.

StackOverflow.

 * = contains the feature
                                     SQLEXPR    SQLEXPRWT   SQLEXPRADV
----------------------------------------------------------------------------
SQL Server Core                         *           *           *
SQL Server Management Studio            -           *           *
Distributed Replay – Admin Tool         -           *           *
LocalDB                                 -           *           *
SQL Server Data Tools (SSDT)            -           -           *
Full-text and semantic search           -           -           *
Specification of language in query      -           -           *
some of Reporting services features     -           -           *

show databases


SELECT * FROM sys.databases
go

Select first char of every column


/* NOTE: fieldName should not contain any quotes. */
select top 5 LEFT(fieldName, 1) from tableName
GO

/* Same effect. */
select top 5 SUBSTRING(colName, 1, 1) from tableName
GO

SQLexpress and LocalDB

MSDN blogs.

SQL Server Express is a free edition of SQL Server.

LocalDB, an improved SQL Express.

LocalDB is created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express.

Developers can continue using it in production, as LocalDB makes a pretty good embedded database too.