SQL Saturday - T-SQL Basic Features

Basic fеаtures specific for MS-SQL. Overview of basic fеаtures and tips that will help developers that are just entering SQL programming or transitioning from other RDBMS to get to know the power of T-SQL.

Transcript

1. MS-SQL Basic Features .NET Framework and T-SQL Tips & Tricks
2. twitter: @rosenkolev rosenkolev.blogspot.com rosen.kolev@hotmail.com About me
7. Agenda Show some tips that can be of help and learn cool and interesting things
8. Plan We ENEMY Attack
9. Plan • Some well known features • Some less known features • Some examples • Not too much description
10. Functions
11. getdate() 2014-12-23 16:48:25.550
12. newid() 724553BD-AE6C-4957-9C45-F121BEF6AAAF
13. Custom Functions
14. Custom Function CREATE FUNCTION Sum(@A INT, @B INT) RETURN INT AS BEGIN RETURN (@A + @B) END
15. Custom Functions in .NET
16. Custom Function in .NET [SqlUserDefinedAggregateAttribute] public struct MyMethod { public void Init(); public void Accumulate ( type value[, type value, ...]); public void Merge( udagg_class value); public return_type Terminate(); }
17. Stored Procedure
18. Stored Procedures A stored procedure is a subroutine available to applications that access a relational database system.
20. CREATE PROCEDURE InsertIntoRoles @Id int @Name nvarchar(50) AS BEGIN INSERT INTO [Roles] VALUES (@Id, @Name) END Stored Procedure Definition
21. Default values CREATE PROCEDURE InsertIntoRoles @Id int = 1 @Name nvarchar(50) = GenerateName() AS BEGIN INSERT INTO [Roles] VALUES (@Id, @Name) END
22. .NET Calling SqlCommand and SqlCommandBuilder.DeriveParameters
23. Demo
25. What if Roles.Name type is change?
26. Table Scaled type Custom table types defined and used in a DB
27. Table Scaled type CREATE TYPE dbo.[MyType] AS TABLE ( -- Columns.... )
28. Procedure with table type CREATE PROCEDURE UpdateRoles ( @Role Role READONLY ) AS BEGIN … END
29. Demo
30. Merge Insert/Update/Delete records from one table based on some conditions from a second table
31. Merge MERGE [Destination Table] AS Target USING [Source Table] AS Source ON Target.KeyField = Source.KeyField WHEN MATCHED THEN ... WHEN NOT MATCHED BY TARGET THEN ... WHEN NOT MATCHED BY SOURCE AND Target.IsActive=1 THEN ...
32. Demo
33. over (partition by) Group the query into partitions so you can execute a function over them.
34. over (partition by) It is explicitly designed not to reduce the result set.
35. Demo
36. Dynamic SQL commands
37. Dynamic SQL commands DECLARE @cmd nvarchar(100) = N'SELECT * FROM Sales ORDER BY ' + @Order; EXEC(@cmd) @Order = N’1; DROP TABLE Sales’
41. Dynamic SQL commands Use sp_exectesql
42. Dynamic SQL commands N'1; DROP TABLE Sales'
43. Dynamic SQL commands sp_executesql OPTION (RECOMPILE)
44. Execution Plan
45. Execution Plan The Query Execution Plans describe the steps and the order used to access or modify data in the database.
47. Execution Plan
48. Execution Plan
49. Service Broker
50. SQL Server Service Broker Processes can send and receive guaranteed, asynchronous messaging
51. Table Types
52. ##, #, @Table, In Memory Tables @, ## and # define scope
53. ##, #, @Table, In Memory Tables # - Local temporary tables are visible only to the same user during the same connection to an instance ## - Global temporary tables are visible to any user and any connection after they are created
54. ##, # vs @Table Variable Tables can be accessed only in the scope of the query block DECLARE @MyTable Table ( … )
55. ##, # vs @Table Table variables are not visible in inner stored procedures and in exec statements.
56. ##, # vs @Table Procedure with a temporary table can’t be pre- compiled.
57. ##, # vs @Table Transaction logs are not recorded for the table variables.
59. In Memory Table Table that its full content is loaded in the memory.
60. In Memory Table CREATE TABLE [dbo].[hash] ( c2 NCHAR(48) NOT NULL ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
61. Keyword ‘With’
62. ‘With’ - Define one or more common table expression
63. Common Table Expression (CTE) • Create a resultset • Must be used right away • Can be recursive
64. Common Table Expression (CTE) WITH Sales_CTE (SalesPersonID, NumberOfOrders) AS ( SELECT SalesPersonID, COUNT(*) FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID ) SELECT AVG(NumberOfOrders) AS "Avg Sales" FROM Sales_CTE;
65. Common Table Expression (CTE) WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports ORDER BY ManagerID;
66. Common Table Expression (CTE) WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports ORDER BY ManagerID;
67. Some Tips • Pivot and Unpivot. • Indexes where and why? • Constraint what kind and how to use them? • Cascade delete.
68. Some Tips ➢ Know your joins ➢ Do not use ‘*’ ➢ Know Your DBMS Limitation ➢ Try to normalize your DB
69. Some Tips ❖ Know your data types ❖ Don’t use EXISTS when you can use join ❖ ORDER BY is slow ❖ Do not use IF/ELSE if you can
70. Relational database are not called relational, because they have relation between tables
73. Sponsors Gold Sponsors: Bronze Sponsors: Swag Sponsors: