Benutzer:MovGP0/SQL
Zur Navigation springen
Zur Suche springen
SQL
[Bearbeiten | Quelltext bearbeiten]Create Table
[Bearbeiten | Quelltext bearbeiten]-- 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
[Bearbeiten | Quelltext bearbeiten]- 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
Update
[Bearbeiten | Quelltext bearbeiten]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
[Bearbeiten | Quelltext bearbeiten]CREATE PROCEDURE DeletePerson
@Id BIGINT
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Person
WHERE Id = @Id
END
GO
Get
[Bearbeiten | Quelltext bearbeiten]CREATE PROCEDURE GetPersonById
@Id BIGINT
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName
FROM People
WHERE Id = @Id
END
GO
Joins[2]
[Bearbeiten | Quelltext bearbeiten]
|
SELECT *
FROM TableA A
LEFT JOIN TableB B
ON A.Key = B.Key
|
|
SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Key = B.Key
|
|
SELECT *
FROM TableA A
RIGHT JOIN TableB B
ON A.Key = B.Key
| |||||||||
|
SELECT *
FROM TableA A
LEFT JOIN TableB B
ON A.Key = B.Key
WHERE B.Key IS NULL
|
|
SELECT *
FROM TableA A
RIGHT JOIN TableB B
ON A.Key = B.Key
WHERE A.Key IS NULL
| |||||||||||
|
SELECT *
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Key = B.Key
WHERE A.Key IS NULL
OR B.Key IS NULL
|
|
SELECT *
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Key = B.Key
|
Normalized vs. Denormalized
[Bearbeiten | Quelltext bearbeiten]Normalized
[Bearbeiten | Quelltext bearbeiten]Advantages | Disadvantages |
---|---|
|
|
|
|
|
|
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 |
Denormalized
[Bearbeiten | Quelltext bearbeiten]Advantages | Disadvantages |
---|---|
|
|
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
- Normalized database optimized for Online Transaction Processing (OLTP)
- Denormalized database optimized for Queries
- Update using SQL Server Integration Services (SISS)[4]
- Get data from normalized database
- Denormalize data
- insert denormalized data into denormalized database
- 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
- Vertial Partitioning
Scaling Out and Sharding
[Bearbeiten | Quelltext bearbeiten]- 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]
Availability
[Bearbeiten | Quelltext bearbeiten]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
Wording
[Bearbeiten | Quelltext bearbeiten]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. |
NULL's
[Bearbeiten | Quelltext bearbeiten]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
|
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]- 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
[Bearbeiten | Quelltext bearbeiten]- 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.
References
[Bearbeiten | Quelltext bearbeiten]- ↑ SET TRANSACTION ISOLATION LEVEL (Transact-SQL). In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
- ↑ Guía visual de SQL joins. In: WordPress. Abgerufen am 11. Juli 2014 (spanisch).
- ↑ Appendix A - Replicating, Distributing, and Synchronizing Data. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch, Data Replication in Azure).
- ↑ SQL Server Integration Services. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
- ↑ Indexes. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
- ↑ Partitioned Tables and Indexes. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
- ↑ Query Processing Enhancements on Partitioned Tables and Indexes. In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).
- ↑ Getting Started with Azure SQL Data Sync. In: Windows Azure. Microsoft, abgerufen am 18. Juli 2014 (englisch).
- ↑ AlwaysOn Failover Cluster Instances (SQL Server). In: MSDN. Microsoft, abgerufen am 18. Juli 2014 (englisch).