Benutzer:MovGP0/SQL

aus Wikipedia, der freien Enzyklopädie
Zur Navigation springen Zur Suche springen
-- 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
  • Queries are blocked until transactions are complete
  • use SET TRANSACTION ISOLATION LEVEL[1] 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

[Bearbeiten | Quelltext bearbeiten]
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
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
CREATE PROCEDURE DeletePerson
    @Id BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Person
    WHERE Id = @Id
END
GO
CREATE PROCEDURE GetPersonById 
	@Id BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT FirstName, LastName
	FROM People
	WHERE Id = @Id
END
GO
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
  • 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
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

[Bearbeiten | Quelltext bearbeiten]
  • 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[3]
    • Use BASE when data is allowed to appear later, like in billing systems
  • Provide two Databases
  • Maintain additional indexes[5] 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[6][7]
      • put rows in separate databases
      • partition function determines in which database the data is inserted
  • 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[8]

Options:

  • Use AlwaysOn Failover Cluster Instances[9]
  • Replicate data
    • only Eventual Consistent (latency in replication)
    • may require additional infrastructure, like Reliable Messaging Queue systems to execute Create, Update, and Delete operations
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

[Bearbeiten | Quelltext bearbeiten]
  • 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.
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

[Bearbeiten | Quelltext bearbeiten]
SQL Server 2014
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
  1. SET TRANSACTION ISOLATION LEVEL (Transact-SQL). In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
  2. Guía visual de SQL joins. In: WordPress. Abgerufen am 11. Juli 2014 (spanisch).
  3. Appendix A - Replicating, Distributing, and Synchronizing Data. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch, Data Replication in Azure).
  4. SQL Server Integration Services. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
  5. Indexes. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
  6. Partitioned Tables and Indexes. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
  7. Query Processing Enhancements on Partitioned Tables and Indexes. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
  8. Getting Started with Azure SQL Data Sync. In: Windows Azure. Microsoft, abgerufen am 18. Juli 2014 (englisch).
  9. AlwaysOn Failover Cluster Instances (SQL Server). In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).