AAtsushi's Blog
Database

Book Notes: SQL Server Transact-SQL Programming Practical Development Guide

→ 日本語版を読む
  • Overview
    • Instance
    • Users
    • Instance connection authentication methods
    • Creating each user
    • Schema
    • SQL Server tools
      • SSMS
      • SQL Server Profiler
    • Database objects
    • System tables
    • System stored procedures
    • Data types
      • String types
      • Numeric types
      • Date types
    • Keys and constraints
    • Server operations management
    • Roles
      • Fixed server roles
        • Adding members to fixed server roles
        • Creating user-defined database roles and granting permissions
      • Fixed database roles
      • Application roles
    • Database backup
      • Recovery models
      • Backup types
    • Viewing error logs
    • SQL query history
    • Data import/export
    • sqlcmd
    • Connecting from another computer
    • Character encoding
    • Copying tables
    • Temporary tables
    • Session ID
    • Transactions
    • Transact-SQL
    • Stored procedures
      • Creating stored procedures
      • Executing stored procedures
      • Stored procedure return values
        • OUTPUT keyword
    • Stored functions
      • Scalar-valued functions
      • Table-valued functions
    • Cursors
    • Dynamic SQL
    • CTE
    • Table variables

Overview

Notes on important points from "SQL Server Transact-SQL Programming Practical Development Guide."

Also notes on things not covered in the book that I looked up out of curiosity.

Instance

  • A SQL Server instance can have multiple instances per physical (or virtual) server machine
  • One instance can have multiple databases
  • "Server name" = "instance name"
  • There are 2 types of instances:
    • Default instance
      • Only one per server machine
    • Named instance
      • Multiple can be created per server machine
  • Even on the same server machine, databases with the same name can be created if instances are different
  • SQL Server instances run as a Windows OS Service
    • When SQL Server needs to be restarted for configuration changes, restart the Service
    • For Express Edition, the Service display name is "SQL Server (SQLEXPRESS)"

Users

Two types exist:

  • User for connecting to the instance (SQL Server authentication user)
  • User for using databases

Instance Connection Authentication Methods

  • Windows Authentication
    • Connect to SQL Server using the Windows OS login account
  • SQL Server Authentication
    • Specify a username and password

Creating Each User

※ Not covered in this book.

Refer to the sites below.

Note: SQL Server authentication must be enabled before creating users!

SQLServer認証のユーザーを作成してみた｜ITエンジニアとして経験・学習したこと

データベースのユーザーとデータベースへのログインユーザーの作成 : SQL Server Tips | iPentec

Schema

※ Not covered in this book.

  • A container-like structure for managing tables separately within a database
  • The default schema is dbo
  • The "Default schema" can be changed in the database user's property settings
  • When creating a table, you can specify a schema other than the default by using [schema_name].[table_name]

既定のスキーマを設定しているのにテーブルにアクセスできない[SQL Server] | zenmai software

To create a schema: In SSMS, go to "Security" → "Schemas" → "New Schema."

SQL Server 2017でスキーマを作成する方法 | .NETコラム

SQL Server Tools

SSMS

Skip

SQL Server Profiler

  • A tool that traces queries executed against SQL Server and lets you view the trace results
  • Can be used to investigate which queries are taking a long time

Database Objects

System Tables

Representative system tables:

  • What tables, views, and stored procedures exist in the database
    • SELECT * FROM sys.objects;
  • List of tables
    • SELECT * FROM sys.tables;
  • List of databases
    • SELECT * FROM sys.databases;
  • Dependencies between database objects
    • SELECT * FROM sys.sysdepends;

System Stored Procedures

  • Get list of server-level roles
    • EXECUTE sp_helpsrvrole;
  • Get list of users belonging to a role
    • EXECUTE sp_helpsrvrolemember;

Data Types

String Types

  • Strings
    • CHAR, VARCHAR, TEXT
  • Unicode strings
    • NCHAR, NVARCHAR, NTEXT
  • Binary strings
    • BINARY, VARBINARY, IMAGE

String literals are enclosed in single quotation marks.

Numeric Types

  • BIGINT, INT, SMALLINT, TINYINT
  • BIT
  • DECIMAL, NUMERIC
  • MONEY, SMALLMONEY
  • FLOAT, REAL
    • These data types are often used in scientific computing. For processing monetary amounts in accounting systems, avoid using them as they can cause errors of 1 yen.

Date Types

  • DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET

Keys and Constraints

Skip

Server Operations Management

  • Immediately after installing SQL Server on Windows, an admin user called "sa" is created by default
  • .mdf is the database file, .ldf is the transaction log file
    • When creating a new database in SSMS, the file save path can be changed

Roles

ロールの追加 - SQL Server | Microsoft Learn

  • Server-level roles
    • Fixed server roles
    • User-defined server roles
  • Database-level roles
    • Fixed database roles
    • User-defined database roles
  • Application roles

Fixed Server Roles

サーバー レベルのロール - SQL Server | Microsoft Learn

  • sysadmin
    • Members of the sysadmin fixed server role can perform any activity on the server.
  • serveradmin
    • Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
  • securityadmin
    • Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.
  • processadmin
    • Members of the processadmin fixed server role can end processes running in an instance of SQL Server.
  • setupadmin
    • Members of the setupadmin fixed server role can add and remove linked servers using Transact-SQL statements. (sysadmin membership is required when using Management Studio.)
  • bulkadmin
    • Members of the bulkadmin fixed server role can execute the BULK INSERT statement.
  • diskadmin
    • The diskadmin fixed server role is used for managing disk files.
  • dbcreator
    • Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
  • public
    • Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on an object when you want the object to be available to all users. You cannot change membership in public.

Adding Members to Fixed Server Roles

ロールの追加 - SQL Server | Microsoft Learn

Creating User-Defined Database Roles and Granting Permissions

※ Not covered in this book.

【SQL Server】ロール(データベースロール)を作成して権限を付与する | 現場で使える！ SQL Server実践ガイド

  1. Create a role

CREATE ROLE (Transact-SQL) - SQL Server | Microsoft Learn

CREATE ROLE role_name

  1. Assign permissions to the role

GRANT (Transact-SQL) - SQL Server | Microsoft Learn

GRANT SELECT,INSERT,UPDATE,DELETE,ALTER TO role_name

  1. Add members to the role

ALTER ROLE (Transact-SQL) - SQL Server | Microsoft Learn

ALTER ROLE role_name ADD MEMBER database_principal

  1. Check role permissions

【SQL Server】ユーザーやロールに付与されている権限を確認する | 現場で使える！ SQL Server実践ガイド

SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('role0419');

  1. Get list of members in a role

sp_helprolemember (Transact-SQL) - SQL Server | Microsoft Learn

EXEC sp_helprolemember;

Fixed Database Roles

データベース レベルのロール - SQL Server | Microsoft Learn

  • db_owner
    • Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
  • db_securityadmin
    • Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
  • db_accessadmin
    • Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
  • db_backupoperator
    • Members of the db_backupoperator fixed database role can back up the database.
  • db_ddladmin
    • Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. Members of this role can potentially elevate privileges by working with code that may run with high privileges and their actions should be monitored.
  • db_datawriter
    • Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. In most use cases, this role is combined with db_datareader membership to allow reading the data to be modified.
  • db_datareader
    • Members of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.
  • db_denydatawriter
    • Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
  • db_denydatareader
    • Members of the db_denydatareader fixed database role cannot read any data in the user tables and views within a database.

Application Roles

アプリケーション ロール - SQL Server | Microsoft Learn

An application role is a database principal that enables an application to run with its own, user-like permissions. Application roles are activated using sp_setapprole, which requires a password.

Database Backup

Recovery Models

復旧モデル (SQL Server) - SQL Server | Microsoft Learn

  • Simple recovery model
    • Restore using backup files only. Changes since the most recent backup are not protected.
  • Full recovery model
    • Restore using both transaction log files and backup files
  • Bulk-logged recovery model

Backup Types

In the full recovery and bulk-logged recovery models, restore is performed using full backups, differential backups, and transaction log backups.

Viewing Error Logs

SQL Server エラー ログの表示 (SSMS) - SQL Server | Microsoft Learn

SQL Query History

※ Not covered in this book.

SQL Server で実行された SQL を SQL で取得する方法 - Project Group

SELECT st.text
      ,last_execution_time
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE  last_execution_time >= '2023/01/01 00:00:00'
ORDER BY last_execution_time

Data Import/Export

SQL Server インポートおよびエクスポート ウィザードを起動する - Integration Services (SSIS) | Microsoft Learn

sqlcmd

Skip

Connecting from Another Computer

レッスン 2: 別のコンピューターからの接続 - SQL Server | Microsoft Learn

  1. Enable TCP/IP in "SQL Server Configuration Manager"
  2. Listen on a specific port
  3. Open the port in the firewall

Character Encoding

SQLServer日本語コードの確認方法 - Qiita

SQLServer UTF8へ文字コード変換

Copying Tables

SELECT.INTO ステートメント (Microsoft Access SQL) | Microsoft Learn

SELECT * INTO new_table FROM source_table

※ Note that no keys or constraints are inherited!!!

Temporary Tables

  • Local session temporary table
    • #table_name
  • Global session temporary table
    • ##table_name

Session ID

  • Can check session ID
    • PRINT @@spid;
  • Each query window has a different session ID

Transactions

※ Not covered in this book.

BEGIN TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

BEGIN TRANSACTION transaction_name

COMMIT TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

COMMIT TRANSACTION transaction_name

ROLLBACK TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

ROLLBACK TRANSACTION transaction_name

  • TRUNCATE, CREATE TABLE, and DROP TABLE are DDL, but in SQL Server, transaction control is possible

Transact-SQL

  • Extended SQL (dialect) in SQL Server

  • Declaring variables

    • DECLARE @variable_name [data_type]

  • Assigning to variables

    • SET @variable_name = [value]
    • SELECT @variable_name = [value]

  • Displaying variable values

    • PRINT @variable_name

  • Switching the connected database

    • USE [DB name];

  • Comments

    • -- comment
    • /* comment */

  • Checking the success/failure of the last Transact-SQL statement executed

@@ERROR の使用 | Microsoft Learn

* `PRINT @@ERROR`
* 0 = success, non-0 = failure
  • TRY - CATCH

Transact-SQL での TRY...CATCH の使用 | Microsoft Learn

    BEGIN TRY
        SELECT 1/0
    END TRY
    BEGIN CATCH
    SELECT
        ERROR_LINE() AS ERROR_LINE,
        ERROR_MESSAGE() AS ERROR_MESSAGE,
        ERROR_NUMBER() AS ERROR_NUMBER,
        ERROR_PROCEDURE() AS ERROR_PROCEDURE,
        ERROR_SEVERITY() AS ERROR_SEVERITY,
        ERROR_STATE() AS ERROR_STATE
    END CATCH;
  • Getting error information

Transact-SQL のエラー情報の取得 | Microsoft Learn

* ERROR_LINE(): Line number of the query where the error occurred
* ERROR_MESSAGE(): Text of the error message returned to the application
* ERROR_NUMBER(): Error number
* ERROR_PROCEDURE(): Name of the stored procedure or trigger where the error occurred; NULL if the error did not occur inside a stored procedure or trigger
* ERROR_SEVERITY(): Severity
* ERROR_STATE(): State
  • IF

IF...ELSE (Transact-SQL) - SQL Server | Microsoft Learn

  IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
    BEGIN
      SELECT 'Weekend'
    END
  ELSE
    BEGIN
      SELECT 'Weekday'
    END

For operators used in conditional expressions, refer to:

比較演算子 (Transact-SQL) - SQL Server | Microsoft Learn

論理演算子 (Transact-SQL) - SQL Server | Microsoft Learn

  • WHILE

WHILE (Transact-SQL) - SQL Server | Microsoft Learn

* Executes repeatedly as long as the condition expression is true
* CONTINUE and BREAK can also be used

  WHILE Boolean_expression
  BEGIN
    statement_block
  END

Stored Procedures

  • A collection of sequential data processing instructions including sequential execution, branching, and repetition
  • Stored procedures are saved in the database
  • Eliminates the need to repeatedly issue SQL statements

Creating Stored Procedures

ストアド プロシージャの作成 - SQL Server | Microsoft Learn

  • Arguments passed to the stored procedure can be defined
  • Default values for arguments can be set
  • If default values are set, arguments can be omitted when executing the stored procedure
  • Optional parameters must be placed at the end
CREATE PROCEDURE sample_stored_proc
    @param1 INT,
    @param2 = 0 INT
AS
BEGIN
    SELECT * FROM test_table
    WHERE
        id = @param1
        OR
        id = @param2
    ORDER BY id
END

Executing Stored Procedures

  • Execute a stored procedure by passing values to arguments
EXEC sample_stored_proc 1, 2;

Stored Procedure Return Values

  • The return value of a procedure can be stored in a variable by executing the procedure as follows:
DECLARE @name varchar(20);
EXEC @name = sample_stored_proc 1, 2;
PRINT @name

OUTPUT Keyword

ストアド プロシージャからデータを返す - SQL Server | Microsoft Learn

  • If the OUTPUT keyword is specified for a parameter in the procedure definition, the current value of that parameter can be returned to the calling program when the procedure exits.
  • To save the parameter value in a variable usable by the calling program, the calling program must use the OUTPUT keyword when executing the procedure.
  • If OUTPUT is specified for a parameter when calling the procedure but the parameter is not defined with OUTPUT in the procedure definition, an error message is displayed.
  • However, it is possible to define output parameters in a procedure and then execute it without specifying OUTPUT. No error is returned, but the output value cannot be used in the calling program.

Stored Functions

CREATE FUNCTION (Transact-SQL) - SQL Server | Microsoft Learn

  • Stored functions always return a value
  • Stored procedures do not necessarily need to return a value

Scalar-Valued Functions

  • Returns the value of argument @price multiplied by 1.08

  • Return value data type is MONEY

  • Stored function definition:

  CREATE FUNCTION fn_include_tax
  (
      @price MONEY
  )
  RETURNS MONEY
  AS
  BEGIN
  RETURN @price * 1.08;
  END
  • Executing the stored function:
  DECLARE @price_with_tax MONEY;
  EXEC @price_with_tax = fn_include_tax 200;
  PRINT @price_with_tax;

Table-Valued Functions

方法: テーブル値のユーザー定義関数を使用する - ADO.NET | Microsoft Learn

Returns table data.

  • Stored function definition:
  CREATE FUNCTION get_names
  (
      @id INT
  )
  RETURNS TABLE
  AS
  RETURN
      SELECT id
      FROM dbo.test_table
      WHERE id > @id

  • Executing the stored function:

    • Note: execution differs from scalar-valued functions!
SELECT * FROM get_names(1);

Cursors

  • Retrieve records one at a time from SELECT statement results
  • Always release the cursor!!!
-- Define the cursor
-- Specify the SELECT statement whose results will be set to the cursor
DECLARE cur CURSOR FOR
SELECT id, name FROM test_table;

-- Open the cursor
OPEN cur;

DECLARE @id INT;
DECLARE @name varchar(20);

-- Extract data one record at a time from the cursor
FETCH NEXT FROM cur INTO
  @id,
  @name

PRINT @id
PRINT @name

-- @@FETCH_STATUS returns a non-0 value when no more records can be retrieved
WHILE(@@FETCH_STATUS = 0)
BEGIN
  FETCH NEXT FROM cur INTO
    @id,
    @name

  PRINT @id
  PRINT @name
END

-- Close and release the cursor
CLOSE cur;
DEALLOCATE cur;

Dynamic SQL

  • Build SQL statements with Transact-SQL
  • Simply put, it's just assigning string literals (SQL statements) to variables
  • Using IF statements etc., the content of SQL statements can be dynamically changed (the example below is not dynamic though)
-- Declare a variable to store the SQL statement
DECLARE @sql VARCHAR(8000);

-- Initialize
SET @sql = '';

-- Build up the SQL statement
SET @sql = @sql + 'SELECT * FROM test_table';
SET @sql = @sql + ' WHERE id > 1'; -- There is one space before the WHERE clause

-- Execute the SQL
-- When the variable contains a query, parentheses () are required!!!
EXECUTE(@sql)

CTE

  • Stands for Common Table Expressions
  • Essentially a named subquery

Table Variables

table (Transact-SQL) - SQL Server | Microsoft Learn

DECLARE @table_variable_name TABLE (
    [column][data_type],
    [column][data_type],
    [column][data_type]
);
← Back to articles