Benutzer:MovGP0/SQL/Programming

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 •

Programming

DECLARE: Variables

DECLARE @AddressLine1 nvarchar(60) = 'Heldenplatz';

SELECT AddressId, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';

equal to:

DECLARE @AddressLine1 nvarchar(60);
SET @AddressLine1 = 'Heldenplatz';

SELECT AddressId, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';

Retrieve a Value into a Variable

DECLARE @AddressLine1 nvarchar(60);
DECLARE @AddressLine2 nvarchar(60);

SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;

SELECT @AddressLine1 AS 'Address1', @AddressLine2 AS 'Address2';

@@ROWCOUNT: Determine how many Rows are affected

DECLARE @AddressLine1 nvarchar(60) = '59. Baker Street';
DECLARE @AddressLine2 nvarchar(60) = 'Reception Desk';

SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;

IF @@ROWCOUNT = 1
   SELECT @AddressLine1 AS 'Address1', @AddressLine2 AS 'Address2'
ELSE
   SELECT 'Either no rows or to many rows found';

IF/THEN/ELSE

DECLARE @QuerySelector int = 3;

IF @QuerySelector = 1 BEGIN
   SELECT TOP 3   ProductId, Name, COlor
   FROM Production.Product
   WHERE Color = 'Silver'
   ORDER BY Name
END 
ELSE BEGIN
   SELECT TOP 3   ProductId, Name, Color
   FROM Production.Product
   WHERE Color = 'Black'
   ORDER BY Name
END;

CASE

CASE

SELECT DepartmentID AS DeptID, Name, GroupName, 
   CASE GroupName -- map group name column
      WHEN 'Research and Development' THEN 'Room A'
      WHEN 'Sales and Marketing' THEN 'Room B'
      WHEN 'Manufacturing' THEN 'Room C'
      ELSE 'ROOM D'
   END AS ConferenceRoom -- into new ConferenceRoom column
FROM HumanResources.Department

Searched CASE

SELECT DepartmentID AS DeptID, Name
   CASE 
      WHEN Name = 'Research and Development' THEN 'Room A'
      WHEN (Name = 'Sales and Marketing' OR DepartmentID = 10) THEN 'Room B'
      WHEN Name LIKE '%T' THEN 'Room C'
      ELSE 'ROOM D'
   END AS ConferenceRoom -- into new ConferenceRoom column
FROM HumanResources.Department

WHILE

-- Declare variables 
DECLARE @AWTables TABLE (SchemaTable varchar(100)); 
DECLARE @TableName varchar(100); 

-- Insert table names into the table variable 
INSERT @AWTables (SchemaTable) 
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME  
   FROM INFORMATION_SCHEMA.tables  
   WHERE TABLE_TYPE = 'BASE TABLE'  
   ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME;

-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0 BEGIN
   -- select first entry from the AWTables list
   SELECT TOP 1   @TableName = SchemaTable 
   FROM @AWTables
   ORDER BY SchemaTable;
   
   -- execute stored procedure to determine table size 
   EXEC sp_spaceused @TableName; 
   
   -- remove entry from the AWTables list
   DELETE @AWTables 
   WHERE SchemaTable = @TableName;
END;