Tuesday, September 30, 2014

0 OUTPUT & MERGE

OUTPUT is used to retrieved the modified data. $actions indicate what action is been taken ( UPDATE, INSERT,DELETE).

MERGE is used to combine the data from different tables.

Create the test data

use [TestDB]
GO
create table dbo.source (
    EmployeeID int,
    EmpolyeeName varchar(20),
    CONSTRAINT source_PK Primary KEY ( EmployeeID)
)
GO
create table dbo.Target (
    EmployeeID int,
    EmpolyeeName varchar(20),
    CONSTRAINT target_PK Primary KEY ( EmployeeID)
)
GO
INSERT INTO dbo.Target( EmployeeID, EmpolyeeName) 
VALUES( 100,'Summery'),(101,'Sunny'),(102,'Skye')
GO
INSERT INTO dbo.source( EmployeeID, EmpolyeeName) 
VALUES( 102,'PO'),(103,'Eva'),(104,'Panda')
GO

image

0 T-SQL Data Aggregation

This is note that I test various Data Aggregation functions in SQL Server.

  • GROUP
  • ROLLUP
  • CUBE
  • GROUPING SET
  • GROUPING
  • GROUPING_ID
  • PIVOT

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.

Product Year Sales
Item 1 2014 100
Item 2 2014 200
Item 2 2013 250
Item 3 2014 50
Item 3 2014 300

Above table will become Pivot as below

  2013 2014
Item 1 0 100
Item 2 250 200
Item 3 0 350

If we UNPIVOT  it, it will change to below

Product Year Sales
Item 1 2014 100
Item 2 2014 200
Item 2 2013 250
Item 3 2014 350

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

USE [AdventureWorks2014]
GO
Create view Sales.vSalesByCategory
AS 
select pc.Name Category,
    ps.Name SubCategory,
    p.Name Product,
    soh.TotalDue 
from sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail sod on soh.SalesOrderID =sod.SalesOrderDetailID
inner join Production.Product p on sod.productID = p.ProductID
inner join Production.ProductSubcategory ps on p.ProductSubcategoryID= ps.ProductSubcategoryID
inner join Production.ProductCategory pc on ps.ProductCategoryID =pc.ProductCategoryID

GROUP

select Category,SubCategory,Product,SUM (TotalDue) 
from Sales.vSalesByCategory
Where Category='Clothing'
GROUP BY Category,SubCategory,Product
ORDER BY  Category,SubCategory,Product

image

0 T-SQL Functions

This article demo some of the common T-SQL Functions.  Purple are new to SQL 2012 which will be cover in here.

  • NULL Functions
    • ISNULL
    • COALESCE
  • Date/Time Functions
    • GETDATE()
    • GETUTCDATE()
    • DATEPART()
    • DATEDIFF()
    • DATEADD()
    • ISDATE()
    • DATEROMPARTS()
    • TIMEFORPARTS()
    • EOMONTH()
    • PARSE()
  • String Functions
    • CHARINDEX()
    • PATINDEX()
    • LEFT()
    • RIGHT()
    • LTRIM()
    • RTRIM()
    • LEN()
    • CONCAT()
    • FORMAT()
  • Data Type Conversions
    • CONVERT()
    • CAST()
    • TRY_PARSE()
    • TRY_CONVERT()
  • Logical Functions
    • CHOOSE()
    • IIF()

NULL FUNCTIONs

Please see here.

Date/Time Functions

Use [AdventureWorks2014]
GO
-- DATE Related functions
SELECT GETDATE()
GO
SELECT GETUTCDATE()
GO
SELECT 
    DATEPART(year, GETDATE() ) AS 'YEAR',
    DATEPART(month,  GETDATE()) AS 'MONTH',
    DATEPART(day,  GETDATE()) AS 'DAY',
    DATEPART(dayofyear,  GETDATE()) 'DAT OF THE YEAR',
    DATEPART(weekday,  GETDATE()) AS 'WEEK DAY'
GO
 
SELECT DATEDIFF(day, '2010/6/11', GETDATE());
GO
SELECT DATEADD(month, 1, GETDATE());
GO
IF ISDATE('2009-05-12 10:19:41.177') = 1
    PRINT 'VALID'
ELSE
    PRINT 'INVALID';
GO
IF ISDATE('I AM STRING') = 1
    PRINT 'VALID'
ELSE
    PRINT 'INVALID';

image

0 New T-SQL Functions in SQL 2012

  • CONCAT function : CONCAT()
  • Format function : FORMAT()
  • Logical Functions : IIF & CHOOSE()
  • Conversion functions: TRY_CONVERT(), TRY_PARSE()
  • Date Functions: DATEFROMPARTS(), TIMEFORMPARTS(), EOMONTH(), PARSE()

CONCAT

CONCAT function will ignore the NULL value.

SELECT CONCAT ( 'Happy ', 'Birthday ', 'Skye', 9, '/', '24' ) AS Result;
 
Create table #temp
    (    name nvarchar(200) NOT NULL,
        middle_name nvarchar(200) NULL,
        last_name nvarchar(200) NOT NULL );
INSERT INTO #temp VALUES('Summer','Tiger','Chen');
INSERT INTO #temp VALUES('Sunny','Dragon','Chen');
INSERT INTO #temp VALUES('Skye',NULL,'Chen');
 
select CONCAT( name,middle_name,last_name) AS RESULT from #temp;
 
DROP table #temp;

image

1 COALESCE and ISNULL

COALESCE and ISNULL are both for NULL evaluation. In many ways they serve the same purpose but they are also difference.

Use [AdventureWorks2014]
GO
-- Result for ISNULL and COALESCE are the same as below
select FirstName, 
    ISNULL(MiddleName,'Not Available') AS 'IsNULL_MiddleName',
    COALESCE(MiddleName, 'Not Available') AS 'COALESCE_MiddleName'
FROM Person.Person
 
--- http://msdn.microsoft.com/en-us/library/ms190349.aspx
 
SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;

The first example we can see they both return the same.

image

From 2nd Query, the COALESCE can take multiple columns and it will return the first not Not NULL

image

Reference

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Monday, September 29, 2014

1 T-SQL Improvement in SQL 2012

This article is summarize what I have learn from 2 Virtual Lab from Microsoft .

SEQUENCE

  • SEQUENCE cross multiple tables
  • SEQUENCE with cycle
  • SEQUENCE in the result set
  • RESET SEQUENCE

SEQUENCE cross multiple tables

--- SEQUENCE 
 
CREATE DATABASE TSQLDB;
CREATE TABLE Employees ( EmployeeId INT NOT NULL PRIMARY KEY,     Name NVARCHAR(255) NULL ); 
CREATE TABLE Contractors ( ContractorId INT NOT NULL PRIMARY KEY, Name NVARCHAR(255) NULL ); 
 
 
USE TSQLDB; 
 
CREATE SEQUENCE IdSequence AS INT     START WITH 10000    INCREMENT BY 1; 
 
INSERT INTO Employees (EmployeeId, Name)    VALUES (NEXT VALUE FOR IdSequence, 'Jane'); 
INSERT INTO Contractors (ContractorId, Name)    VALUES (NEXT VALUE FOR IdSequence, 'John'); 
SELECT * FROM Employees; 
 
SELECT * FROM Contractors; 
--- Reset sequence
ALTER SEQUENCE IdSequence
RESTART WITH 1 ;
 
select NEXT VALUE FOR IdSequence

image

0 Buffer Pool Extensions in SQL Server 2014

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.

--- Check buffer pool extension configuration 
select * from sys.dm_os_buffer_pool_extension_configuration

image

Sunday, September 28, 2014

0 Microsoft SQL Server Contained Databases

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.

Configuration

sp_configure 'contained database authentication',1;
reconfigure with override 
 
select name,value,value_in_use , description from sys.configurations
where name ='contained database authentication'

image

0 SQL Server authentication mode

SQL Server support 2 authentication mode.

Windows Authentication mode and SQL server/Windows Authentication mode.

image

In Windows authentication mode, SQL Server only allow domain account connect to the SQL Server.

This serve configuration can not be checked or changed via sp_configure.

1 Setup Database alerts

This is my note for how to setup the Database alert with SQL Server.

Create Database Mail profile

--- Create Database account
EXECUTE msdb.dbo.sysmail_add_account_sp
   @account_name = 'SGC4_MAIL',
   @email_address = 'XXXX@gmail.com',
   @display_name = 'SGC4 SQL Server',
   @mailserver_name = 'smtp.gmail.com', 
   @mailserver_type='SMTP',
   @port=587,
   @username='XXXXX@gmail.com',
   @password='XXXXXX',
   @enable_ssl=1;
 
- Create a Database Mail profile
XECUTE msdb.dbo.sysmail_add_profile_sp
   @profile_name = 'SGC4_default',
   @description = 'Profile used for administrative mail.' ;
 
- Add the account to the profile
XECUTE msdb.dbo.sysmail_add_profileaccount_sp
   @profile_name = 'SGC4_default',
   @account_name = 'SGC4_MAIL',
   @sequence_number =1 ;
 
-- Check database account and profile
elect * from msdb.dbo.sysmail_profile;
elect * from msdb.dbo.sysmail_account;
 
-- Send test mail
XEC msdb.dbo.sp_send_dbmail 
profile_name='SGC4_default',
recipients = 'XXXX@XXX.COM',
body='This is test from sp_send_dbmail',
subject='This is test from sp_send_dbmail'
 
-- check the mail result 
elect * from msdb.dbo.sysmail_log
elect mailitem_id,recipients,body,sent_status,sent_date from msdb.dbo.sysmail_allitems

Create operator

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'OncallDBA', 
        @enabled=1, 
        @pager_days=0, 
        @email_address=XXXX@gmail.com'
GO
 
select * from msdb.dbo.sysoperators
;
 

image

Test Operator

You will need to create the mail profiler before this.

exec msdb.dbo.sp_notify_operator @profile_name='SGC4_default',
@name='OncallDBA',
@subject='Test operator.',
@body='Test operator'

image

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 “[476] Database Mail is not enabled for agent notifications.

--- Setup SQL Agent Mail profile 
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1, 
        @databasemail_profile=N'SGC4_default', 
        @use_databasemail=1
GO
select name,value,value_in_use,description from sys.configurations where name='Agent XPs'
GO
EXEC xp_servicecontrol 'querystate','SQLAgent$SGC4SQL2014'
GO
--- use below to enable agent if it is not running
EXEC SP_CONFIGURE 'Agent XPs',1
go
reconfigure with override
go
--- Restart SQL Agent
EXEC xp_servicecontrol 'start','SQLAgent$SGC4SQL2014'
GO
EXEC xp_servicecontrol 'STOP','SQLAgent$SGC4SQL2014'
 

image

Setup Alert

Create the new database <TestDB> with initial size 4MB.

image

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.

--- Create empty test database
USE master;
drop database TestDB;
EXEC msdb.dbo.sp_delete_alert @name=N'DatabaseSizeMonitor';         
create database TestDB;
 
---  Create alert name 'DatabaseSizeMonitor'
--- It should send out alert email to oncall DBA when the size is over 100MB 
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'DatabaseSizeMonitor', 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @performance_condition=N'Databases|Data File(s) Size (KB)|TestDB|>|104800', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'DatabaseSizeMonitor', @operator_name=N'OncallDBA', @notification_method = 1
GO
--- Check the alert
exec msdb.dbo.sp_help_alert @alert_name = 'DatabaseSizeMonitor'
 
--- Check the databse size
EXEC sp_databases 
 
 
use TestDB;
create table t2 ( c1 char(8000))
go
insert into t2 values ( 'Skye')
go 20000
 
select * from sys.sysperfinfo 
where instance_name='TestDB' and counter_name = 'Data File(s) Size (KB)'

image

You can see the performance counter has grow to 163008 and we receive the email for the same value as well.

image

image

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates