Tuesday, September 30, 2014
This is note that I test various Data Aggregation functions in SQL Server.
- GROUPING SET
In the simple GROUP, it returns 27 rows. ROLLUP provides some combination of the sum, it return 37 rows. CUBE provides all different combination of SUM, it returns 126 rows.
GROUPING SET gives ability to choice what returns in the CUBE we want to see.
GROUPING provides additional column to indicate whether that particular column is summary data or not.
GROUPING_ID, similar to GROUPING but give us the ID to identify what kind of summary for that row.
PIVOT functions convert row data in to column.
UNPIVOT functions convert column data back to row. However, if there are 2 row data before, it can not distinguish but only convert one row data back.
Above table will become Pivot as below
If we UNPIVOT it, it will change to below
SQL server can not distinguish there were 2 rows of Item 3 in 2014, so it combine both into one row.
Create the test View
Below is the test View. I didn’t come up with the view myself, it is coming from Suherman, STP
This article demo some of the common T-SQL Functions. Purple are new to SQL 2012 which will be cover in here.
- NULL Functions
- Date/Time Functions
- String Functions
- Data Type Conversions
- Logical Functions
Please see here.
COALESCE and ISNULL are both for NULL evaluation. In many ways they serve the same purpose but they are also difference.
The first example we can see they both return the same.
From 2nd Query, the COALESCE can take multiple columns and it will return the first not Not NULL
Monday, September 29, 2014
This article is summarize what I have learn from 2 Virtual Lab from Microsoft .
- VL: What's new for T-SQL in SQL Server 2012
- PAGING windows in T-SQL
- Exception Handling in SQL Server 2012
- New Functions in SQL Server 2012
- VL: SQL Server 2012: Exploring Transact-SQL Improvements in SQL Server 2012
- Creating and Using Sequence Numbers
- Querying Data Using the Offset/Fetch syntax
- Raising Exceptions Using the Throw Statement
- Discovering Stored Procedure Metadata
- Simplifying T-SQL Queries using OVER Clause Windowing
- SEQUENCE cross multiple tables
- SEQUENCE with cycle
- SEQUENCE in the result set
- RESET SEQUENCE
SEQUENCE cross multiple tables
This is new feature for SQL Server 2014. We can use SSD to extend the buffer pool size. The idea is using the faster SSD , in the event when the physical memory need more space, it will use SSD instead of physical disk. However, for testing purpose, the SQL server can use any disk as extension, it does not limit to SSD only. Database engine would not have idea whether it is SSD or not.
Sunday, September 28, 2014
By default, SQL server required the user has login exist at the server level and the user at the database level in order to access the database. From Microsoft SQL Server 2012, the new concept of the database “Contained Database” has been introduced.
Users can connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it eerier to move the database to another instance.
Contained Databases does not support replication, change tracking , change data capture.
This is my note for how to setup the Database alert with SQL Server.
Create Database Mail profile
You will need to create the mail profiler before this.
Enable SQL Agent
It is very important to bounce the SQLAgent after we setup the mail profiler for the SQL Server.
If that is not being done, we will receive the error “ Database Mail is not enabled for agent notifications. ”
Create the new database <TestDB> with initial size 4MB.
Create the alert that will be trigger when the database grow over 100MB.
Database alert will check base on the performance counter. Then create the test table and insert some data to force the database grow.
You can see the performance counter has grow to 163008 and we receive the email for the same value as well.