Benutzer:MovGP0/SQL

Programmierung

T-SQL / SQL Server

T-SQL Fundamantals Select • Programming • Pitfalls • Query from Multiple Tables • Groups and Summaries • Advanced Select • Aggregation and Windowing • Insert, Update, Delete • Strings • Date and Time • Numbers • Transactions, Locking, Blocking, Deadlock • Tables • Views • Large Tables and Databases • Indexes • Stored Procedures • User-Defined Functions and Types • Triggers • Error Handling • Query Performance and Tuning • Hints • Index Tuning and Statistics • XML • Files, Filegroups, Integrity • Backup • Recovery • Principals and Users • Securables, Permissions, Auditing • Objects and Dependencies SQL Server Performance Tuning Memory Performance Analysis • Disk Performance Analysis • CPU Performance Analysis • Baseline Creation • Query Performance Metrics • Query Performance Analysis • Index Architecture and Behavior • Index Analysis • Database Engine Tuning Advisor • Key Lookups • Statistics, Data Distributuon, and Cardinality • Index Fragmentation • Execution Plan Generation • Execution Plan Cache Behavior • Parameter Sniffing • Query Recompilation • Query Design Analysis • Reduce Query Resource Use • Blocking and Blocked Processes • Causes and Solutions for Deadlocks • Row-By-Row Processing • Memory-Optimized OLTP Tables and Procedures • Database Performance Testing • Database Workload Optimization • SQL Server Optimization Checklist Service Broker Message • Contract • Queue • Service • .NET Integration • Route • Security   Datatypes • Unit Testing • Dependency Injection • Partitioning •

Create Table

-- begin transaction
BEGIN TRANSACTION CreateTables
GO

USE MyDatabase;

-- Table Accounts
IF OBJECT_ID('dbo.Accounts', 'U') IS NOT NULL
   DROP TABLE dbo.Accounts;

CREATE TABLE dbo.Accounts (
	AccountID BIGINT PRIMARY KEY,
	AccountName VARCHAR(20) NULL,
	FirstName VARCHAR(20) NULL, 
	LastName VARCHAR(20) NULL, 
	EMail VARCHAR(100) NULL, 
	PasswordHash CHAR(64) NULL, 
	PortraitImage IMAGE NULL, 
	HourlyRate NUMERIC(9,2) NULL
);
GO

-- EnumTable BugStates
IF OBJECT_ID('dbo.BugStates', 'U') IS NOT NULL
   DROP TABLE dbo.BugStates;

CREATE TABLE dbo.BugStates (
	State VARCHAR(20) PRIMARY KEY
);
GO

-- Table Bugs
IF OBJECT_ID('dbo.Bugs', 'U') IS NOT NULL
   DROP TABLE dbo.Bugs;

CREATE TABLE dbo.Bugs (
	BugID BIGINT PRIMARY KEY IDENTITY(1,1), -- autoincrementing key
	DateReported DATE NOT NULL, 
	Summary VARCHAR(80) NULL, 
	BugDescription VARCHAR(1000) NULL, 
	Resolution VARCHAR(1000) NULL, 
	ReportedBy BIGINT NOT NULL, 
	AssignedTo BIGINT NULL, 
	VerifiedBy BIGINT NULL, 
	State VARCHAR(20) NOT NULL DEFAULT 'New', 
	Priority VARCHAR(20) NULL, 
	Hours NUMERIC(9,2) NULL, 
	-- foreign keys
	FOREIGN KEY (ReportedBy) REFERENCES Accounts(AccountID), 
	FOREIGN KEY (AssignedTo) REFERENCES Accounts(AccountID), 
	FOREIGN KEY (VerifiedBy) REFERENCES Accounts(AccountID), 
	FOREIGN KEY (State) REFERENCES BugStates(State)
);
GO
ALTER TABLE Bugs ADD CONSTRAINT CK_Bugs_ReportedBy CHECK (ReportedBy > 0);
ALTER TABLE Bugs ADD CONSTRAINT CK_Bugs_AssignedTo CHECK (AssignedTo > 0);
ALTER TABLE Bugs ADD CONSTRAINT CK_Bugs_VerifiedBy CHECK (VerifiedBy > 0);
GO

-- Table Comments
IF OBJECT_ID('dbo.Comments', 'U') IS NOT NULL
   DROP TABLE dbo.Comments;

CREATE TABLE dbo.Comments (
	CommentID BIGINT PRIMARY KEY IDENTITY(1,1), -- autoincrementing key
	BugID BIGINT NOT NULL, 
	Author BIGINT NOT NULL, 
	CommtentDate DATE NOT NULL,
	Comment TEXT NOT NULL, 
	-- foreign keys
	FOREIGN KEY (BugID) REFERENCES Bugs(BugID), 
	FOREIGN KEY (Author) REFERENCES Accounts(AccountID)
);
GO
ALTER TABLE Comments ADD CONSTRAINT CK_Comments_BugID CHECK (BugID > 0);
ALTER TABLE Comments ADD CONSTRAINT CK_Comments_Author CHECK (Author > 0);
GO

-- Table Screenshots
IF OBJECT_ID('dbo.Screenshots', 'U') IS NOT NULL
   DROP TABLE dbo.Screenshots;

CREATE TABLE dbo.Screenshots(
	BugID BIGINT NOT NULL, 
	ImageID BIGINT NOT NULL, 
	ScreenshotImage IMAGE NULL, 
	Caption VARCHAR(100),
	-- combined primary key 
	PRIMARY KEY (BugID, ImageID), 
	-- foreign key
	FOREIGN KEY (BugID) REFERENCES Bugs(BugID)
);
GO
ALTER TABLE Screenshots ADD CONSTRAINT CK_Screenshots_BugID CHECK (BugID > 0);
ALTER TABLE Screenshots ADD CONSTRAINT CK_Screenshots_ImageID CHECK (ImageID > 0);
GO

-- Table Tags
IF OBJECT_ID('dbo.Tags', 'U') IS NOT NULL
   DROP TABLE dbo.Tags;

CREATE TABLE dbo.Tags (
	BugID BIGINT NOT NULL, 
	Tag VARCHAR(20) NOT NULL, 
	-- combined primary key
	PRIMARY KEY (BugID, Tag), 
	-- foreign key
	FOREIGN KEY (BugID) REFERENCES Bugs(BugID)
);
GO

-- Table Products
IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL
   DROP TABLE dbo.Products;

CREATE TABLE dbo.Products (
	ProductID BIGINT PRIMARY KEY IDENTITY(1,1) NOT NULL,  -- autoincrementing key
	ProductName VARCHAR(50) NULL
);
GO

-- LookupTable BugsProducts 
IF OBJECT_ID('dbo.BugsProducts', 'U') IS NOT NULL
   DROP TABLE dbo.BugsProducts;

CREATE TABLE dbo.BugsProducts(
	BugID BIGINT NOT NULL, 
	ProductID BIGINT NOT NULL, 
	-- combined primary key
	PRIMARY KEY (BugID, ProductID), 
	-- foreign key
	FOREIGN KEY (BugID) REFERENCES Bugs(BugID), 
	FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
GO

-- commit transaction
COMMIT TRANSACTION CreateTables
GO

Updates

• Queries are blocked until transactions are complete
• use SET TRANSACTION ISOLATION LEVEL to avoid blocking

-- Start transaction
BEGIN TRAN Transaction1

-- execute query
UPDATE Persons.Person
WHERE FirstName = 'John'
AND LastName = 'Miller'
SET LastName = 'Smith'
GO -- execute query

-- execute more queries

-- Rollback the transaction
ROLLBACK TRAN

Create Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.CreatePerson 
	@FirstName VARCHAR(255), 
	@LastName VARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO People (FirstName, LastName)
	VALUES (@FirstName, @LastName)
END
GO

Update

CREATE PROCEDURE [dbo].[UpdatePerson]
    @Id BIGINT,
    @FirstName VARCHAR(255), 
    @LastName VARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE People 
    SET FirstName = @FirstName, LastName = @LastName
    WHERE Id = @Id;
END

Delete

CREATE PROCEDURE DeletePerson
    @Id BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Person
    WHERE Id = @Id
END
GO

Get

CREATE PROCEDURE GetPersonById 
	@Id BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT FirstName, LastName
	FROM People
	WHERE Id = @Id
END
GO

Joins

A A∪B B	SELECT * FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key	A A∪B B	SELECT * FROM TableA A INNER JOIN TableB B ON A.Key = B.Key	A A∪B B	SELECT * FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key
A A∪B B	SELECT * FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL	A A∪B B	SELECT * FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL
A A∪B B	SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL	A A∪B B	SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key

Normalized vs. Denormalized

Normalized

Advantages	Disadvantages
Performs better on concurrent column changes on the same record Adding columns is reasy No need for NULL-able columns	fragmented data reads require joins I/O overhead, when tables are stored on different disks

SalesOrderID TaxAmount 1 15,28 55280 18,97

SalesOrderID DueDate 1 2013-08-07 44280 2013-08-10 44544 2013-08-11

SalesOrderID OrderDate 1 2013-01-07 44280 2013-01-10 44544 2013-01-11

SalesOrderID TotalDue 1 279,99 44280 282,50 44544 155,25

Partial Denormalized

• Fast for data that is often read, but seldom modified

SalesOrderID	TaxAmount	DueDate	OrderDate	TotalDue
1	15,28	2013-08-07	2013-01-07	279,99
44280	18,97	2013-08-10	2013-01-10	282,50
44544	NULL	2013-08-11	2013-01-11	155,25

Denormalized

Advantages	Disadvantages
Scales better for large number of concurrent users Less overhead on queries	Data duplication

SalesOrderID	SalesOrderDetailID	ProductID	OrderQuantity	UnitPrice	TaxAmount	DueDate	OrderDate	TotalDue
5999	450	711	2	20,19	2005-01-07	2005-08-07	3089,91	30100
5999	451	762	1	419,46	2005-01-07	2005-08-07	3089,91	30100
5999	452	754	3	874,79	2005-01-07	2005-08-07	3089,91	30100
5999	453	709	1	5,70	2005-01-07	2005-08-07	3089,91	30100

Performance considerations

• Keep transactions short
• Avoid repeating the same work
  • poorly designed transactions may lead to a deadlock, causing the application to repeat the action
  • ensure that resources are accessed in the same order to reduce deadlocks
• Avoid database triggers over date that is updated frequently
  • Triggers are part of the transaction who caused them to fire
  • Use triggers on denormalized tables with duplicated data in different tables. triggers can ensure that duplicates are updated
• Do not execute actions with an indeterminate running time
  • Transactions may not wait for user data
• Implement Transactions in the database
  • avoid business logic to handle transactions
  • use stored procedures
• Basically Available, Soft state, Eventual consistency (BASE) has higher scalability than Atomicity, Consistency, Isolation, Durability (ACID)
  • BASE Transaction updates only one site. Until data is propagated to all sites, data might be inconsistent.
  • Azure uses Service Bus Topics and Subscriptions
  • Use BASE when data is allowed to appear later, like in billing systems
• Provide two Databases
  • Normalized database optimized for Online Transaction Processing (OLTP)
  • Denormalized database optimized for Queries
  • Update using SQL Server Integration Services (SISS)
    1. Get data from normalized database
    2. Denormalize data
    3. insert denormalized data into denormalized database
• Maintain additional indexes on columns that are regularily queried (ie. lookups from other tables)
  • consider multi-column indexes, when queries rely on multiple columns
  • indexes have performance impact on write, because the index has to be maintained
• Partition database file
  • Vertial Partitioning
    • put tables (and columns) in separate databases, to parallelize I/O (2nd part of query runs on second database, while 1st part of query still runs on the first)
    • combine with partial denormalization to put frequently used columns in different table than infrequent used columns; reducing table size increases caching
  • Horizontal Partitioning
    • put rows in separate databases
    • partition function determines in which database the data is inserted

Scaling Out and Sharding

→ Hauptartikel: Verteilungsmuster

• Scaling Up (better Server) vs. Scaling Out (more Servers)

Sharding (Horizontal Partitioning)

• Each database node contains a different set of tables
• Use when users from different regional locations access different data
• Updating data across different shards reduces performance; may require BASE (less consistency) instead of ACID
• Application needs to know in which shard to find the data, because of network latency

Replication

• Use when the same data is used from different regional locations
• Use Azure SQL Data Sync

Availability

Options:

• Use AlwaysOn Failover Cluster Instances
• Replicate data
  • only Eventual Consistent (latency in replication)
  • may require additional infrastructure, like Reliable Messaging Queue systems to execute Create, Update, and Delete operations

Wording

Abbr.	Meaning	Description
OLTP	online transactional processing	for data entry; not for reporting; optimized for insert, update and delete operations; tree/map data model
DSA	data staging area	data model used in ETL; fully denormalized
DW, DM	data warehouse, data mart	optimized for business intelligence queries; more redundancy and fewer tables; warehouses for whole organization, marts for a department; data is pre-summarized and uses a star model
BISM	business intelligence semantic model	Analysis Services (MDX) or PowerPivot (DAX)
DM	data mining	algorithms for trend analysis, like clustering and decision trees
ETL	extract, transform, and load	Integration Services transform OLTP model to DW model
MDX	multidimensional expressions	multidimensional data analysis for BI Professionals and IT
DAX	data analysis expressions	tabular data analysis for business users
DMX	data mining extensions	language for data mining

Default Databases

• Azure allows access to master table only

Name	Description
master	instance metadata, server configuration, information about databases in the instance, initialization information
Resource	hidden/read-only, definitions of system objects
model	template for new databases
tempdb	tempory data; work tables, data to sort, row versioning, etc.
msdb	data for SQL Server Agent; jobs, schedules, alerts, replication, database mail, service broker, backups, etc.

NULL's

Scalar Expressions

Expression	Expected	Actual
NULL = 0	TRUE	NULL
NULL = 12345	FALSE	NULL
NULL <> 12345	TRUE	NULL
NULL + 12345	12345	NULL
NULL || 'string'	'string'	NULL
NULL = NULL	TRUE	NULL
NULL <> NULL	FALSE	NULL

Boolean Expressions

Expression	Expected	Actual
NULL AND TRUE	FALSE	NULL
NULL AND FALSE	FALSE	FALSE
NULL OR FALSE	FALSE	TRUE
NULL OR TRUE	TRUE	TRUE
NOT(NULL)	TRUE	NULL

Internetquellen

• TSQL Tutorial. Abgerufen am 18. Juli 2014 (englisch, T-SQL-Referenz). 
• SQL Tutorial. In: w3schools. Refsnes Data, abgerufen am 18. Juli 2014 (englisch). 
• SQL Server Language Reference. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch). 
• SQL Server Customer Advisory Team. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch, Tipps für die Optimierung von Azure und SQL Server). 
• SQL Books. In: simple talk. Abgerufen am 18. Juli 2014 (englisch, kostenlose E-Books zu SQL Server). 
• Tim Ford: SQL Server 2012: Periodic Table of Dynamic Management Objects. (PDF) Abgerufen am 18. Juli 2014 (englisch). 

Literatur

SQL Server 2014

• Ross Mistry, Stacia Misner: Introducing Microsoft SQL Server 2014. 2014, ISBN 978-0-7356-8475-1. 

SQL Server 2012

• Kalen Delaney: Microsoft SQL Server 2012 Internals. 2013, ISBN 978-0-7356-5856-1. 
• Itzik Ben-Gan: Microsoft SQL Server 2012 T-SQL Fundamentals. 2012, ISBN 978-0-7356-5814-1. 
• Grant Fritchey: SQL Server 2012 Query Performance Tuning. 3. Auflage. 2012, ISBN 978-1-4302-4203-1. 
• Christian Bolton, Justin Langford, Glenn Berry, Gavin Payne, Amit Banerjee, Rob Farley: Professional SQL Server 2012 Internals and Troubleshooting. 2012, ISBN 978-1-118-17765-5. 
• Jason Brimhall, David Dye, Jonathan Gennick, Andy Roberts, and Wayne Sheffield: SQL Server 2012 T-SQL Recipes. 3. Auflage. 2012, ISBN 978-1-4302-4200-0. 
• Jason Strate, Ted Krueger: Expert Performance Indexing for SQL Server 2012. 2012, ISBN 978-1-4302-3741-9. 

SQL Server 2008

• Rod Colledge: SQL Server 2008 Administration in Action. 2009, ISBN 978-81-7722-982-0. 
• Teo Lachev: Applied Microsoft SQL Server 2008 Reporting Services. 2008, ISBN 978-0-9766353-1-4. 
• Denny Cherry: Securing SQL Server. 2. Auflage. 2012, ISBN 978-1-59749-947-7.