Download - Anything SQL: Lightning Talks
![Page 1: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/1.jpg)
Anything-SQL
Lightning Talks
![Page 2: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/2.jpg)
My data-mart is better than your data warehouseGogula Aryalingam Business Intelligence
01
Navantis
![Page 3: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/3.jpg)
![Page 4: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/4.jpg)
Thank you
My data-mart is better than your data warehouse
![Page 5: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/5.jpg)
TRY… CATCH…
Shiham Samsudeen T-SQL
02
Zone 24x7
![Page 6: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/6.jpg)
TRY… CATCH… Alien ?
• Your Code in the TRY block• Check for the Errors in the CATCH block
Can be found in Modern Programming Languages
![Page 7: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/7.jpg)
TRY… CATCH…T-SQL Style
• Your SQL Statement in the TRY block• Check for the Errors in the CATCH block
Find it in Transact-SQL
sql_statement: any Transact-SQL statement
statement_block : any group of Transact-SQL statements
in a batch or enclosed in a BEGIN…END block.
![Page 8: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/8.jpg)
Why your code FAILED?List of SQL Server functions to identify:
ERROR_NUMBER() returns the number of the error
ERROR_SEVERITY() returns the severity of the error
ERROR_STATE() returns the error state number
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred, this will be NULL if you run an ad-hoc SQL statement
ERROR_LINE() returns the line number inside the routine that caused the error
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
![Page 9: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/9.jpg)
NEED to see some T-SQL
Nice Result
Check out what being selected
out?
![Page 10: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/10.jpg)
What can we do with this ?
I will Hold Error Info
I will Fill the Error Info
![Page 11: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/11.jpg)
Error Occurs…Recording Error Info
Take Out the Error Info.
Results!
Error 1
Error 2
Error 3
![Page 12: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/12.jpg)
Information is Wealth! the ability to log all errors plus any other information you want to capture like
user name, host name etc with 1 Stored Proc that you can call from everywhere
Take Home with You,
the ability to do anything with the logged
data Once YOU have it.
I CAN NOT do ANYTHING on SOMETHING until I have that THING!
![Page 13: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/13.jpg)
Thank you
Try… Catch…
![Page 14: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/14.jpg)
Parameter Sniffing
Susantha Bathige T-SQL
03
Pearson Lanka
![Page 15: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/15.jpg)
Thank you
Parameter Sniffing
![Page 16: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/16.jpg)
Data Compression
Sudesh Wijayatilake Performance Tuning
04
Finlays Colombo PLC
![Page 17: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/17.jpg)
The Feature• The Data Compression feature of
SQL Server, can reduce the storage footprint of your databases.
– Often, data compression may lead to degrade in CPU performance due to compress/decompress; • However, SQL Server 2008 data
compression simply stores data more efficiently in the rows and pages, which means that the in-memory storage and IO Operations data management is efficient.
![Page 18: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/18.jpg)
Mainly 2 Types of Compression
• SQL Server 2008 supports 2 specific types of data compression methods:– Row Compression
• lower level which stores the fixed character strings by using variable-length format by not storing the blank characters. NULL and 0 values across all data types are optimized and take no bytes.
– Page Compression• Is Higher level compressing & uses two types of compression
– Prefix - Prefix compression works on common values pattern across all rows on the page
– Dictionary - Works on exact values match pattern across all pages.
![Page 19: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/19.jpg)
Considerations• Data compress very well:
– Columns with numeric or fixed-length character data types where most values don’t require all the allocated bytes: For example, integers where most values are less than 1000
– Null-able columns where a significant number of the rows have a NULL value for the column
– Significant amounts of repeating data values or repeating prefix values in the data
• Some patterns of data that do not benefit much from compression are:
– Columns with numeric or fixed-length character data types where most values require all the bytes allocated for the specific data type
– Not much repeating data– Repeating data with non-repeating prefixes– Data stored out of the row– FILESTREAM data
• Compression is Version dependant [Cannot be decompressed on another version]
![Page 20: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/20.jpg)
Implement Data compression
• You can implement both types of data compression when you create a table or index, or by altering existing tables and indexes. You can do this by using Transact-SQL code or the Data Compression Wizard.
![Page 21: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/21.jpg)
USE [AdventureWorks] Go
/*Estimating how much space can be saved by row or page*/EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL, NULL,'ROW' ;Go
EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL,NULL, 'PAGE' ;GO---------------------------------------------------------------------------------------------------------------/*Enabling Table compression by row or page */ALTER TABLE Sales.SalesOrderDetailREBUILD WITH (DATA_COMPRESSION = ROW); –OR
ALTER TABLE [Sales].[SalesOrderDetail] REBUILD PARTITION = ALLWITH (DATA_COMPRESSION = PAGE)
![Page 22: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/22.jpg)
Referred from
http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
![Page 23: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/23.jpg)
Bolt of Lightening
![Page 24: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/24.jpg)
Queries and Share Your Findings
• Please email me [email protected]
–OR upload to our SQL Server Universe sitehttp://www.sqlserveruniverse.com/ forums
![Page 25: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/25.jpg)
Thank you
Data Compression
![Page 26: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/26.jpg)
Table Variables, The good and the bad
Supun Thrikawala Performance Tuning
05
CAM Management Solutions
![Page 27: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/27.jpg)
Thank you
Table variables, the good and the bad
![Page 28: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/28.jpg)
SQL Server Standards
Avantha Siriwardana General
06
CAM Management Solutions
![Page 29: Anything SQL: Lightning Talks](https://reader035.vdocuments.us/reader035/viewer/2022062406/5589e381d8b42a87558b462c/html5/thumbnails/29.jpg)
Thank you
SQL Server Standards