MSSQL

Terminology

• Transaction: Treating multiple data changes as a single logical unit of work so that everything is either completed or rolled back, maintaining data integrity

• COMMIT: All transaction modifications are permanently applied as part of the database, and the resources used by the transaction are released

• ROLLBACK: Reverts to the starting state and cancels all modifications made during the transaction

• Trigger: When data in a specific table is modified, it automatically cascades the update internally to other related tables or columns, or maintains integrity

• Cursor: The position in memory where a SQL statement is executed It can directly access and fetch the result of a SQL statement that exists in memory (SELECT * FROM A) The currently processed row

SQL Server Overview

• Database: A collection of data organized so that the data and information required by users can be efficiently stored, managed, and

                    integrated
  

• Relational database

• A database in which related data is not all contained in a single table but managed across several different tables, organizing the data by

  assigning some relationship between the data

• SQL Server service

  • As a database engine, it processes Transact-SQL statements and manages all files that make up the databases on the server.

• SQL Server Agent

  • A service related to automated management; it automates administration by creating jobs, alerts, operators, and so on.

  • For example, when the log exceeds 90%, it raises an alert, and when the alert occurs, it backs up the database log.

• MS DTC (Microsoft Distributed Transaction Coordinator)

  • A series of operations used to process distributed data as a single transaction

• Full-Text Search

  • A Microsoft search service that queries text and strings under various conditions

  • To use this feature, you must create and maintain a full-text index.

• Analysis Server

  • A server component of Microsoft SQL Server 2000 Analysis Services that provides fast analytical access to data warehouses and data marts

• Database objects

1. Index: An object used to enable fast data retrieval or to enforce data integrity

   Data integrity: Maintaining and guaranteeing the accuracy and consistency of data

2. View: A virtual table defined to reuse frequently used or complex queries

3. Trigger: A special kind of stored procedure that runs automatically when data is changed

4. Rule: Performs the same role as a CHECK constraint and is used by binding to a table column or a user-defined data type.

   EXEC sp_bindrule 'ZipCode_rule', 'ZipCode'

• Purpose of system databases

1. Master: Stores all data related to the server.

2. TempDB: Used as a temporary workspace when creating temporary tables or when accompanying clauses such as ORDER BY, DISTINCT

3. Model: The database that serves as the model when creating a new database

4. MSDB: Records content related to automated management.

5. Distribution: Used for log distribution when replication is configured.

Transact-SQL Basics

• Types of SQL statements

• Data Manipulation Language (DML): Data insert, query, update, delete (INSERT, SELECT, UPDATE, DELETE)

• Data Definition Language (DDL): Defines the characteristics and properties of a database, and creates, modifies, and deletes tables, indexes, views, etc. (CREATE, ALTER, DROP)

• Data Control Language (DCL): Controls access permissions on database objects (GRANT, REVOKE, DENY)

• Three ways to alias a column

SELECT GETDATE() AS DateTime, 2 Quantity, UnitPrice=450

• Identifiers

• Database object names

• When a reserved word or special character is included, a delimiter is used to distinguish the identifier.

• Delimiters are [] , "" (SET QUOTED_IDENTIFIER ON session option; when OFF it is used as a string)

SELECT *

FROM [MY TABLE] // distinguished when a whitespace character is included

WHERE [ORDER] = 10 // distinguished for some reserved words

• Logical operators: precedence - NOT, AND, OR

• ORDER BY (sorting queried data)

• Constraint: ntext, text, image cannot be used in an ORDER BY clause.

SELECT type, AVG(price) AS avgPrice

FROM titles

WHERE royalty = 10

GROUP BY type

HAVING AVG(price) < 80

• DISTINCT and GROUP BY clauses

• DISTINCT is mainly used to query UNIQUE (duplicate-removed) columns or records

• GROUP BY groups the data and returns the result.

e.g.)

-- Removing duplicate data using DISTINCT SELECT DISTINCT deptno FROM emp;

-- Removing duplicate data using GROUP BY SELECT deptno FROM emp GROUP BY deptno;

DEPTNO

30
20
10

1. When is each used?

   GROUP BY: Used when distinguishing specific groups using aggregate functions

   DISTINCT: Used when removing duplicate data without distinguishing specific groups

e.g.) SELECT COUNT(DISTINCT d.deptno) "Deduplicated Count", COUNT(d.deptno) "Total Count" FROM emp e, dept d WHERE e.deptno = d.deptno;

-- When an aggregate function is needed, GROUP BY must be used. SELECT deptno, MIN(sal) FROM emp GROUP BY deptno;

• Difference between SELECT INTO / INSERT INTO

• SELECT INTO: Used when the source exists and you want to create a new destination table (only the source table is needed - since it is copied)
• INSERT INTO: Must be used when both the source table and the destination table already exist

1. When is each used?

• SELECT INTO: Mainly used to create a temporary table for sub-tasks while keeping the source table intact during complex operations.

• INSERT INTO: Used to copy data from the source table

2. How to use SELECT INTO

   -Used when the source exists and you want to create a new destination table

   -SELECT * INTO AA FROM A

     -Creates a table named AA with the same columns and data as table A
   

   1-1 If you want to bring only specific columns of table A,

         SELECT * INTO AA
   
            FROM ( SELECT COL1, COL2, COL3.....
   
                              FROM AA
   
                        ) AS TEMP
   
    -Brings only specific columns of table A, creates a table named AA, and inserts the data.
   

3. How to use INSERT INTO

• INSERT INTO B SELECT * FROM A

  -In the above, table A and table B must have the same schema. (Same data structure, same format tables)

• INSERT INTO B SELECT COL1,COL2,COL3 FROM A

  -If there are fewer columns than A, specify the columns after SELECT

• Join: An operation that queries two or more tables to produce a single result

1. Inner Join (INNER JOIN) / Outer (OUTER JOIN)

● INNER JOIN

• ANSI standard: SELECT a.id, a.val aval, b.val bval FROM A a INNER JOIN B b ON a.id = b.id;

• Transact-SQL: SELECT a.id, a.val aval, b.val bval FROM A a ,B b WHERE a.id = b.id;

● LEFT OUTER JOIN

• ANSI standard: SELECT a.id, a.val aval, b.val bval FROM A a LEFT OUTER JOIN B b ON a.id = b.id;

• Transact-SQL: SELECT a.id, a.val aval, b.val bval FROM A a , B b WHERE a.id *= b.id;

● RIGHT OUTER JOIN

• ANSI standard: SELECT a.id, a.val aval, b.val bval FROM A a RIGHT OUTER JOIN B b ON a.id = b.id;

• Transact-SQL: SELECT a.id, a.val aval, b.val bval FROM A a , B b WHERE a.id =* b.id;

• UNION

• JOIN: Parallel combination of two tables or results

  UNION: A serial combination that merges query results vertically (top and bottom)

• To use GROUP BY and HAVING clauses on the entire UNION result, use a temporary table or a derived table

SELECT stor_ID AS StoreID, Qty*2 AS Quantity INTO #SalesTemp FROM sales

SELECT StoreID, SUM(Quantity) FROM ( SELECT Stor_ID AS StoreID, Qty AS Quantity FROM sales UNION ALL SELECT * FROM #SalesTemp ) t GROUP BY StoreID

Result)

6380 24 7066 375

• DELETE / UPDATE

• DELETE FROM authors WHERE au_lname = 'McBadden'

• UPDATE authors SET state = 'PC' WHERE state = 'CA'

• INSERT

• When the INSERT statement is used with a VALUE clause, the INTO clause can be omitted

• Columns that do not need to be specified in an insert

  1. Columns that allow NULL

  2. Columns with a default value

  3. Columns with an auto-increment attribute

  4. Columns with Rowversion or Timestamp

• TRUNCATE TABLE / DELETE

• Same as a DELETE statement without a WHERE clause

• DELETE: Auto-increment attribute is retained / TRUNCATE: Auto-increment attribute is reset

• DELETE is fast and uses fewer transaction log resources -After DELETE removes a row, it records a transaction log entry for each row

• TRUNCATE does not log per-row deletions, so DELETE triggers do not fire

  Trigger: Something that automatically cascades to other related tables or columns internally when data in a specific table is modified, in order to maintain integrity

DB Creation and Management

• Database: A unit of management that maintains consistency by grouping objects related to a business unit you want to manage into a single logical

                    storage unit, and it is also a unit of recovery
  

• Basic composition of a database: File group / data file / log file

  1. File group: A logical collection of multiple data files

                  Can be considered for improving performance by distributing across disks.
     

  2. Database file: Can be divided into data files and log files, referring to the physical storage unit that records data.

                             Primary file: Stores the database startup information and data / Secondary files: Other files
     
                             Transaction log file: Stores the log information needed for data recovery
     

  3. Extent: The basic unit allocated to tables and indexes (one extent consists of 8 contiguous pages (64KB))

                When the previously allocated storage space is insufficient, a new extent is allocated and used starting from the first page.
     

  4. Data file: The physically reserved space where extents can be allocated

  5. Page: A page is the basic unit of data storage in SQL Server

• Creating a database

  1. Database: Composed of multiple data files and log files

  2. When there are multiple physical drives, data files are distributed across multiple file groups to distribute hard disk I/O.

     The log size varies depending on the amount of data modification and the log backup schedule, and is basically 10~25%

CREATE DATABASE [Testpubs] ON PRIMARY (NAME = N'Testpubs_Data' ,FILENAME = N'C:\TEMP\Testpubs_Data.MDF' ,SIZE = 5, MAXSIZE=100 ,FILEGROWTH=10%) LOG ON (NAME = N'Testpubs_Log' ,FILENAME = N'C:\TEMP\Testpubs_Log.LDF' ,SIZE = 2 ,FILEGROWTH=1MB)

● ON: Explicitly defines the data file where the data portion is stored

● PRIMARY: Defines the primary file

● NAME: The logical name

● LOG ON: Explicitly defines the disk file where the database log is stored.

● FOR LOAD: An option to recover from database backup data and initialize it

● FOR ATTACH: An option to attach a database from an existing set of operating system files

• Increasing file size and adding a new file

ALTER DATABASE [Testpubs] MODIFY FILE (NAME = 'Testpubs_Data' ,SIZE = 10)

ALTER DATABASE [Testpubs] ADD FILEGROUP [Second]

ALTER DATABASE [Testpubs] ADD FILE (NAME = N'Testpubs_Data2' ,FILENAME = N'C:\Temp\Testpubs_Data2.NDF' ,SIZE = 1, FILEGROWTH = 10% ) TO FILEGROUP [Second]

• Using file groups

• File groups explicitly allocate space to store tables and indexes, as well as BLOB-type data, i.e., Image, text, and ntext data types.

• BLOB (Binary Large Object)

ALTER DATABASE [Testpubs] ADD FILEGROUP [FG_Index] ALTER DATABASE [Testpubs] ADD FILEGROUP [FG_BLOB]

ALTER DATABASE [Testpubs] ADD FILE (NAME = N'Textpubs_Data3' ,FILENAME = N'C:TEMP\Testpubs_Data3.NDF' ,SIZE = 1 ,FILEGROWTH = 10%) TO FILEGROUP [FG_Index]

-- BLOB stands for Binary Large Object, referring to image, text, ntext data types
ALTER DATABASE [Testpubs] ADD FILE (NAME = N'Textpubs_BLOB' ,FILENAME = N'C:TEMP\Testpubs_BLOB.NDF' ,SIZE = 1 ,FILEGROWTH = 10%) TO FILEGROUP [FG_BLOB]

CREATE TABLE [pub_info] ([pub_id][char] (4) PRIMARY KEY ON [FG_Index] NOT NULL ,[pub_name]char NOT NULL ,[logo][image] NULL ,[pr_info][text] NULL ) ON [PRIMARY] TEXTIMAGE_ON [FG_BLOB]

• Shrinking a database

1. Automatic file shrink: Periodically shrinks the file while leaving 25% free space and returns it to the OS.

DBCC SHRINKFILE(Testpubs_Data, 25) - Runs periodically at 30-minute intervals

2. Manual file shrink

● EMPTYFILE: Moves all data to another file in the same file group and leaves the file empty

                     For the purpose of physically moving the file to another space and removing it

DBCC SHRINKFILE(Testpubs_Data2, EMPTYFILE)

ALTER DATABASE Testpubs REMOVE FILE Testpubs_Data2

● NOTRUNCATE: Retains the freed file space in the file and does not return the freed space to the operating system.

● TRUNCATEONLY: Returns unused space in the file to the operating system, reducing the file size without moving data.

                            When using TRUNCATEONLY, target_size is ignored.

DBCC SHRINKFILE(Testpubs_Data, 5, [EMPTYFILE | NOTRUNCATE|TRUNCATEONLY )

3. Manual database shrink

• Used to shrink all data files and log files in the specified database.

• ALTER DATABASE and DBCC SHRINKFILE cannot make it smaller than the final size explicitly set.

DBCC SHRINKDATABASE(Testpubs, 25) Shrinks while leaving 25% free space (NOTRUNCATE , TRUNCATEONLY )

• Changing and deleting a database

  1. Changing a file group

• sp_help: Reports information about a database object, a user-defined data type, or a data type

• Data_located on_filegroup -FG_Index

ALTER DATABASE Testpubs MODIFY FILEGROUP [FG_Index] DEFAULT

CREATE TABLE Test( id int)

EXEC sp_help Test

2. Changing the database name

• When changing the database name, switch to single-user mode, and after the change, disable single-user mode again

• ROLLBACK IMMEDIATE: Add the option to roll back incomplete transactions.

ALTER DATABASE Testpus SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC sp_renamedb 'Testpubs', 'TestpubsNew'

EXEC sp_dboption TestpubsNew, 'Single user', FALSE

3. Deleting a database

DROP DATABASE TestpubsNew

• Database options

  1. Automatic options

  ● AUTO_CLOSE: When the last user of the database disconnects and the database shuts down, the database resources are released.

  ● AUTO_CREATE_STATISTICS: Statistics are automatically created for columns used in predicates.

  ● AUTO_UPDATE_STATISTICS: Automatically updates statistics when data changes and the existing statistics are out of date.

  ● AUTO_SHRINK: Periodically inspects unused space in the database's data files and log files and shrinks it.

  2. Cursor options

  ● CURSOR_CLOSE_ON_COMMIT: When a transaction is committed, open cursors are automatically closed.

  ● CURSOR_DEFAULT_LOCAL/GLOBAL: The scope of the configured cursor is applied locally, or applied globally for the connected session

  3. Recovery options / State options (SINGLE, READ_ONLY) / Nonlogged Operation (when no record is left in the log file) / WITH

ALTER DATABASE Testpus SET SINGLE_USER WITH ROLLBACK AFTER 300(sec)

EXEC sp_renamedb 'Testpubs', 'TestpubsNew'

EXEC sp_dboption TestpubsNew, 'Single user', FALSE

• The role of the log

  • The log records all modification operations on the database, so that when the server fails and cannot COMMIT, it performs a ROLLBACK to

    maintain data consistency.

  • Items recorded in the transaction log

    The start and end of a transaction, all data modifications and changed content, all extent allocations and deallocations

    Creation or deletion of tables or indexes

Data Types and Type Conversion

• Variable-length data types

  Variable-size data types: varchar, varbinary, nvarchar

  Data types with non-fixed and unspecifiable size: text, ntext, image, sql_variant

  • text and image are stored in a separate space, not within the table's storage, operating with a 16-byte starting address of that storage.

• When to use variable-length vs. fixed-length?

  Variable-length: When you define a large fixed-character type but use small strings

  Fixed-length: In places where the input format (3 characters) is fixed, using a fixed length can yield better performance

• Unicode

SELECT ASCII('A') AS ASCIIA ,UNICODE(N'가') AS UNICODE가

SELECT CHAR(65) AS CHAR65 ,NCHAR(44032) AS CHAR44032

=65, 44032 / A, 가

• Numeric data types

1. Exact numbers: decimal, numeric

2. Approximate numbers: float, real(float(24))

CREATE Table TestFloat (xNumeric Numeric(7,3) //DEFAULT is total precision 18, scale 0 ,xDecimal Decimal(7,3) ,xFloat Float //Stores an approximate value and is calculated internally. ,xReal Real)

INSERT TestFloat VALUES(3,3,3,3) INSERT TestFloat VALUES(12.3,12.3,12.3,12.3) SELECT * FROM TestFloat

3.000 3.000 3 3 12.300 12.300 12.3000000001 12.3

• datetime (milliseconds) / smalldatetime (minutes)

• Large data types

  • To store large data, use the text, ntext, and image data types

  • Can store 2G; does not directly store the data page. It stores only a 16-byte pointer, and the actual data is kept in a separate storage space.

• Other data types

  1. Timestamp and rowversion (8 Byte)

  ● Version management of each row's data within the database

  ● Displays an automatically generated binary number guaranteed to be unique within the database

  ● Only one can be set per table

  ● You can find the last applied version number using the @@DBTS function.

  ● Simple comparisons using =,<>,<,>,<=,>= can be used

  ● The version does not increment on deletes, only on inserts.

CREATE TABLE TestTimeStamp (ID int PRIMARY KEY ,Name char(10) ,xTimeStamp Timestamp)

CREATE TABLE TestRowversion (ID int PRIMARY KEY ,Name char(10) ,xRowVersion rowversion)

INSERT TestTimeStamp(ID, NAME) Values(1, '홍길동') INSERT TestRowversion(ID, NAME) Values(1, '홍길동')

SELECT @@DBTS

2. Sql_variant

   ● A data type intended to support storing multiple data types in a single column

   ● ntext, text, image, timestamp, sql_variant cannot be specified

• sql_variant constraints and characteristics

  1. It must be converted to a base data type value before being used in an operation.

     SELECT CONVERT(int, @MyVar1) + CONVERT(int, @MyVal2)

  2. It cannot have a length greater than 8016 bytes.

  3. The IDENTITY property cannot be set

  4. Can be used as a primary key or reference key

  5. You cannot change to the text, ntext, image, timestamp, or sql_variant type using the ALTER TABLE statement.

DECLARE @MyVal1 AS sql_variant DECLARE @MyVal2 AS sql_variant SET @MyVal1 = 1 SET @MyVal2 = '2' SELECT CONVERT(int, @MyVal1) + CONVERT(int, @MyVal2)

3. Uniqueidentifier

   • A data type used to represent the GUID type

CREATE TABLE MyUniqueTable (UniqueID UNIQUEIDENTIFIER DEFAULT NEWID() ,Characters char(10))

• TABLE

  • Declares a variable with a table structure

  • A table variable is automatically removed as soon as the batch processing completes.

DECLARE @TableVar TABLE (ID int PRIMARY KEY, Name nchar(10))

INSERT INTO @TableVar VALUES (1, N'홍') INSERT INTO @TableVar VALUES (2, N'승아')

• User-defined data types

  • sp_bindrule: Binds a rule to a column or an alias data type

CREATE RULE ZipCode_rule AS @value LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]'

EXEC sp_bindrule 'ZipCode_rule', 'ZipCode'

• Data type conversion

  1. Implicit conversion

     SELECT 4/3, 4/3.0 -1 / 1.3333 SELECT '1' + 2 , '1'+'2'-3 , 12

  2. NULL operations: A NULL operation is always NULL

     When SET CONCAT_NULL_YIELDS_NULL ON -string NULL

  3. Explicit conversion (CAST, CONVERT)

  • STR(float_expression [ , length [ ,decimal ] ] )

float_expression: An expression of an approximate (float) data type with a decimal point. If it exceeds 10 digits, * is displayed length The total length; the default is 10. decimal If it is larger than 16 digits, it is truncated

e.g.) SELECT '<' + STR(11.1234) + '>' < 11>

Creating and Using Tables and Constraints

• Object identifiers

  • Servers, databases, tables, views, columns, etc. all have a unique name, i.e., an identifier.

  • The delimiters [] and "" can be used

    "" -can be used when QUOTED_IDENTIFIER is ON

  1. Identifier rules

  ● An identifier consists of 1-128 characters

  ● @ (at sign): local variable, parameter / # : temporary table, procedure / ## : global temporary table

  ● Transact-SQL @@ : @@IDENTITY

  ● Reserved words, embedded spaces, and special characters cannot be used

  CREATE TABLE [$Emplayee Data] -can be used as an identifier when a delimiter is used

• Temporary tables, table variables

  1. Commonality: Both are used to temporarily use and then discard a table

  2. Differences

     • Temporary table: DROP Table to remove it forcibly; automatically deleted when the session disconnects

     • Table variable: Automatically discarded when it crosses a GO separator in the query analyzer.

CREATE TABLE #cnst_example

(

...

) INTO #cnst_exam

DECLARE @cnst_example TABLE

(

...

)

• Creating a table

1. [ON { <filegroup| DEFAULT }]

CREATE TABLE [pub_info] ([pub_id] [char] (4) PRIMARY KEY ON [FG_Index] NOT NULL // pub_id FG_Index file group ,[pub_name] [char] (10) NOT NULL ,[logo] [image] NULL ,[pr_info] [text] NULL) ON [PRIMARY] // only Default is Primary TEXTIMAGE_ON [FG_BLOB] // logo, pr_info file group

2. NOT FOR REPLICATION

• For data inserted through replication, insert it as is without receiving a new increment value

CREATE TABLE Sales1 (SaleID INT IDENTITY(1, 1) NOT FOR REPLICATION, CHECK NOT FOR REPLICATION (SaleID <= 99999) ,SalesRegion CHAR(2) ,CONSTRAINT ID_PK PRIMARY KEY (SaleID))

SET IDENTITY_INSERT Sales ON

INSERT INTO sales1(SaleID, SalesRegion) SELECT s2.SaleID, s2.SalesRegion FROM sales2 S2

SET IDENTITY_INSERT Sales OFF

INSERT INTO Sales1 VALUES('H0');

1 'ho'

2 'hk'

1000 'hu'

1001 'ha'

3 'hi'

3. ROWGUIDCOL

   • ROWGUIDCOL can only be specified on a uniqueidentifier column, indicating that the column is a unique identifier column

   • When the property is set, the column value is not generated automatically; it is generated when inserted via INSERT.

CREATE TABLE TestGUIDCOL (Seq int IDENTITY PRIMARY KEY ,GUIDCOL uniqueidentifier DEFAULT(NEWID()) ROWGUIDCOL ,Message varchar(5000))

INSERT TestGUIDCOL (Message) VALUES('ROWGUIDCOL TEST') INSERT TestGUIDCOL (Message) VALUES('IDENTITYCOL TEST')

SELECT ROWGUIDCOL, Message FROM TestGUIDCOL WHERE IDENTITYCOL = 1 // statement that compares the IDENTITY row

4. CONSTRAINT

• Defines a constraint (constraints: PRIMARY KEY, UNIQUE, CHECK constraints, etc.)

CREATE TABLE Sales

(

...

, CONSTRAINT PK_Sales PRIMARY KEY NONCLUSTERED

(

SaleDate, SlipNo

)

)

• IDENTITY

● Defines a property that automatically increments and decrements an integer value in a column

● Defined on a column via CREATE or ALTER TABLE statements

● Reason for use: Used to create a unique identifier and use it as a primary key

1. IDENTITY property

CREATE TABLE TestIDENTITY (ID int PRIMARY KEY IDENTITY(1000, 2), NAME char(10)) - seed value, increment

2. @@IDENTITY: Returns the last ID.

   When SET IDENTITY_INSERT is ON, the IDENTITY value can be modified

   =If you want to set it as a unique value, set it as a PRIMARY KEY or UNIQUE KEY

3. IDENTITYCOL reserved word

• Returns the IDENTITY column row. (Used with WHERE)

  WHERE IDENTITYCOL = 1

4. DBCC CHECKIDENT

• Reason for use: When the IDENTITY property exceeds its maximum, an overflow error occurs; the seed value can be set

  DBCC CHECKIDENT(TestIDENTITY, RESEED, 500)

5. @@IDENTITY function

• Returns the last auto-increment value applied in the current session

• Computed columns

  • A virtual column that is not physically stored (used with a computation expression)

CREATE TABLE Sales

( CustomerId int not null

Amt AS ROUND(Qty*Price, -2)

)

INSERT INTO Sales VALUES(1115)

• Modifying / deleting a table

CREATE TABEL TestAlterTable( column_a INT)

ALTER TABLE TestAlterTable ADD column_b varchar(20) NULL

ALTER COLUMN column_b int

DROP COLUMN column_b

DROP TABLE TestAlterTable

• Constraints

● ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED

● Data integrity: A method for maintaining consistent data

                       Maintains the standards to be observed through constraints, default values, and data insert/update/delete.

● Referential integrity: Maintains the relationship between primary keys and foreign keys (FOREIGN KEY, CHECK)

                   CASCADE DELETE, CASCADE UPDATE

● Entity integrity: Defines the unique entity of a specific table (PRIMARY, UNIQUE)

● Domain integrity: Ensures that data in a given column is valid (CHECK, DEFAULT, NOT NULL)

1. Constraints

• NOCHECK option: Creates the constraint without checking existing problems that violate referential integrity in a table that does not have a FOREIGN KEY

ALTER TABLE [Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY

(

[OrderID]

) REFERENCES [Orders]

(

[OrderID]

)

- DEFAULT constraint

  ● When using an INSERT statement, if the value to be entered is not specified, the DEFAULT value is used instead

- CHECK constraint

ALTER TABLE ZipCode ADD CONSTRAINT chk_ZipCode //creates the check constraint chk_ZipCode and validates on INSERT. CHECK( phone LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

- PRIMARY KEY / UNIQUE

  ● PRIMARY: A unique identifier, used as a candidate key for referential integrity, does not allow NULL

  ● UNIQUE: A unique identifier, used as a candidate key for referential integrity, allows NULL

- FOREIGN KEY constraint

  ● Can reference only tables within the same database on the same server

  ● Implements referential integrity by creating a foreign key

      ALTER TABLE [Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY

(

[OrderID]

) REFERENCES [Orders]

(

[OrderID]

) ON DELETE CASCADE

// ON UPDATE CASCADE

 ON DELETE NO ACTION
 Specifies that an error is raised and the UPDATE or DELETE statement is rolled back when you try to delete/update a row that contains a key

 referenced by a foreign key in an existing row of another table

● When the DELETE/UPDATE CASCADE constraint is not defined (using a transaction)

   BEGIN TRAN

DELETE [Order Details] WHERE OrderID= 10248

DELETE Orders WHERE OrderID = 10248

   END TRAN

System-Supported Functions and Operators

• Numeric operation functions

1. CEILING, FLOOR

   • SELECT CEILING(11.55), CEILING(11.11), FLOOR(11.11), FLOOR(11.55) -12, 12, 11, 11

2. ROUND

   • SELECT ROUND(11.22, 1) , ROUND(1111.2, -2) -11.20, 1100.0

3. RAND

   • INSERT INTO TestSales VALUE(RAND()*26) -generates a random value between 0~25

4. POWER(X, Y) Y-th power, SQRT square root

• String functions

1. LTRIM, RTRIM (removes whitespace)

SELECT '<' + LTRIM(' SQL서버 ') + '>' - <SQL 서버 >

 ,'<' + RTRIM('    SQL서버     ') + '>'     - <      SQL 서버>

 , '<' + LTRIM(RTRIM('    SQL서버     ')) + '>'   - <SQL 서버>

2. REPLICATE

SELECT REPLICATE('OK',4), 'A' + SPACE(5)+'B' - OKOKOKOK A B

3. REPLACE, STUFF

   SELECT REPLACE('대한 민국 만세','민국','독립 만'), - 대한 독립 만 만세

   STUFF('대한 민국 만세','민국',3,4,'독립 만') - 대한독립 만만세

4. CHARINDEX, PATINDEX

   CHARINDEX('com', title) -returns the starting position of the pattern

   PATINDEX('%c[a-k]n%] -various options can be used.

5. DIFFERENCE (4 if the match is high, 0 if low, does not support Korean)

   DIFFERENCE('can', 'con') -3

• Date and time functions

  1. DATEADD

     SELECT MonthStartDate = DATEADD(dd, 1, date)

  2. DATEDIFF

     DATEDIFF(dd, newdate, olddate)

  3. DATEPART

     DATEPART(mm, GETDATE())

  4. DATENAME

     DATENAME(dw, GETDATE())

• System functions

  1. CAST and CONVERT

  CAST(11.11 AS char(10) , CONVERT(char(10) , 11.11)

  2. COALESCE - Returns the first non-NULL value among several column values

  3. @@IDENTITY Returns the last Identity value assigned in the session

  4. ISDATE, ISNULL(ISNULL(qty, 0)), ISNUMERIC

  5. @@ROWCOUNT

  SELECT GETDATE() - 2001-10-22~~

  SELECT @@ROWCOUNT

  6. @@TRANCOUNT

  BEGIN TRANSACTION

   ...
  

  SELECT @@TRANCOUNT - 1

  BEGIN TRANSACTION

  ....

  SELECT @@TRANCOUNT - 2

  7. NULLIF (returns NULL if the two values are equal, otherwise returns the first value)

     NULLIF(10, 10), NULLIF(10, 20)

• Operators

  1. Bitwise operators

  • Performed on bit, binary, varbinary, excluding int, smallint, tinyint, image

  • Used to manage the information values of system tables. (0001 autoclose, 0100 sp_dboption) Sets system information via OR operations

  2. Logical operators

  • ALL: Becomes true when all compared values are true.

    WHERE ID(1,7,9) ANY(SELECT ID FROM TestTemp - 4,6,8) -9
    

  • ANY, SOME: Becomes true if even one of the values being compared is true. SOME (ISO standard synonym)

    WHERE ID(1,7,9) ANY(SELECT ID FROM TestTemp - 4,6,8) -7,9
    

  • EXISTS (true if data exists in the subquery), IN (true if it matches any one of the listed values)

    IF EXISTS(SELECT * FROM SALES WHERE stor_id = '6380')

    WHERE state IN('CA', 'IN', 'MD')

Advanced Queries

• CASE function

• Evaluates a list of conditions and returns one of several result expressions

1. Simple CASE function (compares against a simple expression)

SELECT au_fname, au_lname

CASE state

WHEN 'CA' THEN 'Califonia

END AS StateName

FROM authors

2. Searched CASE function

SELECT title_id, CAST(title AS varchar(20)) AS title, price, PriceCategory = CASE WHEN price IS NULL THEN N'Undetermined' WHEN price < 10 THEN N'Low-priced' WHEN price >= 10 AND price < 20 THEN N'Mid-priced' ELSE N'High-priced' END
FROM titles ORDER BY price

• CASE function using a correlated subquery

  ● Outer query: A query that contains a sub (inner) query / Inner query (subquery): A query within a query

  • The subquery is executed under the influence of the outer query

SELECT stor_id, stor_name,

CASE WHEN (SELECT COUNT(*) From Sales WHERE stor_id = s.stor_id) 3 THEN N'Active'

ELSE 'Inactive'

END AS Status

FROM Stores s

• Subqueries

  • A SELECT query nested inside a SELECT, INSERT, UPDATE, DELETE statement or another subquery (up to 32 levels of nesting are possible)

  • SELECT: Returned and expressed as a single value

    WHERE: A single column or a set of rows (using EXISTS, IN, ANY, ALL logical operators)

    FROM: Multiple rows can come from multiple columns or expressions (derived table)

  1. Subquery restrictions

  A subquery can include an ORDER BY clause only when there is a TOP clause

  2. When repeatedly recalculating and reusing, compute the value first and reuse it.

  DECLARE @SumQty int

  SELECT @SumQty = SUM(qty) FROM Sales

  SELECT stor_id, Qty, @SumQty FROM Sales

  3. Subquery using IN (can be handled with a join)

  SELECT title_id, title, pub_id, price

  FROM titles

  WHERE title_id IN( SELECT title_id FROM Sales WHERE qty>40)

  =

  SELECT titles.title_id, title, pub_id, price

  FROM titles INNER JOIN Sales

  ON titles.title_id = Sales.title_id AND qty>40

  4. Getting the difference set with a subquery (using the NOT IN statement)

  SELECT title_id, title FROM titles

  WHERE title_id NOT IN( SELECT title_id FROM Sales)

  5. Using EXISTS

  SELECT DISTINCT pub_name FROM publishers WHERE EXISTS ( SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type='business')

= Using IN

SELECT DISTINCT pub_name FROM publishers WHERE pub_id IN ( SELECT pub_id FROM titles WHERE type='business' )

• Derived tables

  • Refers to a subquery used in the FROM clause.

SELECT s.stor_id, stor_name, SumQty FROM stores t, (SELECT stor_id, SUM(qty) AS SumQty FROM sales GROUP BY stor_id ) AS s WHERE t.stor_id = s.stor_id

SELECT s.stor_id, stor_name, SUM(qty) AS SumQty FROM stores t, sales s WHERE t.stor_id = s.stor_id GROUP BY s.stor_id, stor_name

• Partial-range query

• A partial range can be set using the TOP command.

• Stored procedure: A set of query statements used to batch-process some operation.

CREATE PROCEDURE up_GetOrders @Move nchar(1) = N'F', @OrderID int = NULL AS IF @Move = N'F' SELECT TOP 10 * FROM Orders ORDER BY OrderID DESC

IF @Move = N'L' SELECT _ FROM ( SELECT TOP 10 _ FROM Orders ORDER BY OrderID ASC) t ORDER BY OrderID DESC

=EXEC up_GetOrders 'L'

• Correlated subquery

SELECT so.* FROM Sales so

WHERE so.SaleQty = ( SELECT MAX(si.SaleQty) FROM Sales si

WHERE so.ProductID = si.ProductID)

• Dynamic query: You can dynamically build a query string and execute it with the EXECUTE statement.

Advantages: Increases query reuse efficiency, improving DB performance.

       Complex queries and frequently used queries can be simplified and used.

• STR: Truncates the decimal part and produces a 10-character string.

CREATE PROC Sales_TopN @CNT AS int AS DECLARE @Str VARCHAR(100) SELECT @Str = 'SELECT TOP ' + STR(@CNT) + 'Stor_id, Qty FROM Sales ORDER BY Qty DESC' EXEC(@Str)

EXEC Sales_TopN 10

• COMPUTE BY

1. Summary of the difference between COMPUTE and GROUP BY

GROUP BY returns a single result set. For each group, there is one row containing the grouping columns and aggregate functions that show the subtotals for that group. The SELECT list can include only the grouping columns and aggregate functions. It does not show the raw data. COMPUTE (BY) shows the raw data, and is often used when querying subtotals and grand totals together with the raw data. The columns in the COMPUTE BY clause must be specified in the ORDER BY clause. SELECT stor_id, ord_num, title_id, payterms, qty FROM sales WHERE stor_id LIKE '706%' ORDER BY stor_id, qty DESC COMPUTE AVG(qty), SUM(qty) BY stor_id

COMPUTE AVG(qty), SUM(qty)

SELECT qty, AVG(qty), SUM(qty) FROM sales WHERE stor_id LIKE '706%' GROUP BY qty ORDER BY qty

Index Design and Usage

• Terminology

• Index page: The page where the actual index is stored

• Data page: The page where the actual data is stored

• Covered Query: A query built as a non-clustered index using a composite key of the queried columns and the condition columns

• Index

• Used to improve the response time of queries

• Reasons for using indexes

• Mainly improves the execution speed not only of data retrieval but also of updates with search conditions

• Used to enforce uniqueness and prevent duplicate data from occurring.

• Disadvantages of indexes

• Indexes require additional storage space, and when there are many indexes, it takes more time to execute insert, update, and delete commands on the data.

• Additionally, more processing time is needed to maintain and manage them.

• Clustered index

• An index that sorts and stores the data rows in the table based on the key value; data within a page is sorted based on the index key

• Getting one piece of data from a large amount of data; since the data itself is sorted, range searches are used more efficiently.

• A case where the data storage and the index storage coexist, and the data is stored in the order of the index.

• Non-clustered index

• An index in which the data storage and the index storage are separated into separate spaces, while holding a pointer that can be used to find the row in the data

  storage

• When a clustered index does not exist, it holds a RID (row identifier) pointer, and when a clustered index exists, it holds the clustered index key value

  as the pointer.

• Index design

● When an index is useful

• When searching for rows that exactly match a specific search key value (WHERE emp_id = 357)

• When searching for rows with a search key value in a specific range (WHERE job_lvl BETWEEN 9 and 12)

• When searching for rows in table T1 that match rows in table T2 based on a join predicate

• When producing query output sorted by a specific column (ORDER BY Qty DESC)

• When ensuring PRIMARY KEY and UNIQUE constraints to enforce uniqueness

• When ensuring referential integrity between two tables where a FOREIGN KEY constraint is defined

● When an index is not useful

• Columns with few distinct values: When there is little uniqueness, such as gender

• Long string data type columns (because there is a limit to the index that can fit on one page)

• Columns that are rarely searched

• Indexes cannot be created on the bit, text, ntext, and image data types

• Do not use indexes on small tables (because index traversal time can be longer than a table scan)

● Additional guidelines for index design

• Since all indexes must be adjusted when the table's data changes, creating many indexes on a table degrades INSERT, UPDATE, and DELETE performance

• Performance improves for queries that do not modify data, such as SELECT statements.

• Data and index storage structure

  ● Clustered table with a clustered index

  • Data rows are stored in order based on the clustered index key.

  ● Table without a clustered index (heap)

  • Not stored in any particular order, and there is no special order for the data page sequence.

  ● Index ID (can be checked in sysindexes)

  • For a table without a clustered index, indid value: 0

  • Clustered index indid value: 1

  • Non-clustered index indid value: 2~250

  • Table with text, ntext, image columns: 255

  ● Root and FirstIAM

  • The root column of sysindexes points to the top of the index B-tree.

    FirstIAM -IAM (collection of the table's data pages) -data row header

• Various ways to search for data

  ● Table scan: In a table without a clustered index, uses the IAM page chain to perform I/O on all pages of the table

                      FirstIAM -brings all pages containing data through the start pointer of the IAM page chain
  

SELECT _ FROM OrderX SELECT _ FROM Orders

● Clustered index scan using IAM

 - When scanning the entire clustered index, it does not use the intermediate-level index of the clustered index but performs I/O on the leaf

   nodes of the clustered index, i.e., all data pages, through the IAM.

● Clustered index with previous/next page pointers

 - In a clustered index, sequentially performs I/O on all pages starting from the first or last page through the previous/next page pointers within

   the data pages. (Queried through the previous and next page pointers)

 - Clustered index: Uses the clustered index key value (coexists with the data page); Non-clustered index: Uses index pages to

                              find the data page

SELECT * FROM Orders ORDER BY OrderID SELECT OrderID FROM Orders ORDER BY OrderID DESC

● Non-clustered index scan

 - A case that can be processed by scanning only the index pages without using an index seek

SELECT COUNT(*) FROM Orders

● Clustered index seek

 -  Performs a B-Tree search from the index root node to obtain the result

SELECT _ FROM OrderX SELECT _ FROM Orders

● Non-clustered index seek

 - Performs a B-Tree search and performs a bookmark lookup operation using the index key or row identifier.

USE Northwind

SELECT * INTO OrderX FROM Orders

CREATE INDEX OrderX_OrderDate ON OrderX(OrderDate)

CREATE INDEX Orders_OD_C ON Orders(OrderDate, CustomerID) GO

EXEC sp_helpindex OrderX EXEC sp_helpindex Orders GO

• Index design

● When an index is useful

• When searching for rows that exactly match a specific search key value (WHERE emp_id = 357)

• When searching for rows with a search key value in a specific range (WHERE job_lvl BETWEEN 9 and 12)

• When searching for rows in table T1 that match rows in table T2 based on a join predicate

• When producing query output sorted by a specific column (ORDER BY Qty DESC)

• When ensuring PRIMARY KEY and UNIQUE constraints to enforce uniqueness

• When ensuring referential integrity between two tables where a FOREIGN KEY constraint is defined

● When an index is not useful

• Columns with few distinct values: When there is little uniqueness, such as gender

• Long string data type columns (because there is a limit to the index that can fit on one page)

• Columns that are rarely searched

• Indexes cannot be created on the bit, text, ntext, and image data types

• Do not use indexes on small tables (because index traversal time can be longer than a table scan)

● Additional guidelines for index design

• Since all indexes must be adjusted when the table's data changes, creating many indexes on a table degrades INSERT, UPDATE, and DELETE performance

• Performance improves for queries that do not modify data, such as SELECT statements.

• Data and index storage structure

  ● Clustered table with a clustered index

  • Data rows are stored in order based on the clustered index key.

  ● Table without a clustered index (heap)

  • Not stored in any particular order, and there is no special order for the data page sequence.

  ● Index ID (can be checked in sysindexes)

  • For a table without a clustered index, indid value: 0

  • Clustered index indid value: 1

  • Non-clustered index indid value: 2~250

  • Table with text, ntext, image columns: 255

  ● Root and FirstIAM

  • The root column of sysindexes points to the top of the index B-tree.

    FirstIAM -IAM (collection of the table's data pages) -data row header

• Various ways to search for data

  ● Table scan: In a table without a clustered index, uses the IAM page chain to perform I/O on all pages of the table

                      FirstIAM -brings all pages containing data through the start pointer of the IAM page chain
  

SELECT _ FROM OrderX SELECT _ FROM Orders

● Clustered index scan using IAM

 - When scanning the entire clustered index, it does not use the intermediate-level index of the clustered index but performs I/O on the leaf

   nodes of the clustered index, i.e., all data pages, through the IAM.

● Clustered index with previous/next page pointers

 - In a clustered index, sequentially performs I/O on all pages starting from the first or last page through the previous/next page pointers within

   the data pages. (Queried through the previous and next page pointers)

 - Clustered index: Uses the clustered index key value (coexists with the data page); Non-clustered index: Uses index pages to

                              find the data page

SELECT * FROM Orders ORDER BY OrderID SELECT OrderID FROM Orders ORDER BY OrderID DESC

● Non-clustered index scan

 - A case that can be processed by scanning only the index pages without using an index seek

SELECT COUNT(*) FROM Orders

● Clustered index seek

 -  Performs a B-Tree search from the index root node to obtain the result

SELECT _ FROM OrderX SELECT _ FROM Orders

● Non-clustered index seek

 - Performs a B-Tree search and performs a bookmark lookup operation using the index key or row identifier.

USE Northwind

SELECT * INTO OrderX FROM Orders

CREATE INDEX OrderX_OrderDate ON OrderX(OrderDate)

CREATE INDEX Orders_OD_C ON Orders(OrderDate, CustomerID) GO

EXEC sp_helpindex OrderX EXEC sp_helpindex Orders GO

• Query statements that are highly likely not to use an index

1. Index usage in negative search conditions

SELECT * FROM orders WHERE OrderDate <'19960704'

2. Search on a transformation of an indexed column

SELECT * FROM orders WHERE CONVERT(char(5), OrderID) LIKE '1024%'

3. Search condition with a mismatched data type

CREATE TABLE TestOrders (OrderID char(5) NOT NULL, ...

SELECT * FROM TestOrders WHERE OrderID = 10500

4. Index usage in a pattern-condition search

SELECT * FROM TestOrder WHERE OrderID LIKE '%500'

• Index creation options

CREATE NONCLUSTERED INDEX zip_ind

ON authors(zip)

WITH FILLFACTOR = 70 -fills with 70 FILLFACTOR, leaving 30% free space

● FILLFACTOR - An option that sets how much the index page is packed or left empty,

                       it uses the leaf-node index pages up to the specified % and leaves the rest empty.

                  -Used to reduce page split operations, since a new page must be allocated when index page space is insufficient

● PAD_INDEX: Changes the level of the remaining index pages, other than the leaf index pages, to the level specified by FILLFACTOR

● IGNORE_DUP_KEY :

-For duplicate data entered by an INSERT operation, it is handled as a warning instead of an error; a duplicate caused by an UPDATE operation is handled as an error. USE tempdb GO

CREATE TABLE emp_pay (employeeID int NOT NULL ,base_pay money NOT NULL ,commision decimal(2,2) NOT NULL) GO

CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay(employeeID) WITH IGNORE_DUP_KEY GO

INSERT emp_pay VALUES(1, 500, .10) -allowed (allowed even with a duplicate key value) INSERT emp_pay VALUES(1, 1000, .05) GO

SET NOCOUNT ON

INSERT emp_pay VALUES(5,0,.03) SELECT @@ERROR GO

UPDATE emp_pay SET employeeID = 1 WHERE base_pay = 500 SELECT @@ERROR GO

SELECT * FROM emp_pay

=Result

Msg 2601, Level 14, State 1, Line 2 Cannot insert duplicate key row in object 'dbo.emp_pay' with unique index 'employeeID_ind'. The duplicate key value is (1).

● DROP_EXISTING / STATISTICS_NORECOMPUTE / SORT_IN_TEMPDB

• DBCC SHOWCONTIG

  • Used to determine whether a table is fragmented. Table fragmentation occurs when processing data modification statements such as INSERT, UPDATE,

    and DELETE on the table.

• DBCC INDEXDEFRAG

  • Re-sorts the leaf-level pages of an index in logical order.

• DBCC DBREINDEX

  • Rebuilds a specific index on a table or all indexes defined on a table.