Task 20: NoSQL vs SQL

SQL vs NoSQL, they’re actually talking about relational versus non-relational databases. That’s what I’m talking about here: Relational databases (such as Oracle, MySQL, and SQL Server) versus newer non-relational databases (such as MongoDB, CouchDB, BigTable, and others).

Popular open source NoSQL data stores include:

• Cassandra (tabular, written in Java, used by Cisco WebEx, Digg, Facebook, IBM, Mahalo, Rackspace, Reddit and Twitter)
• CouchDB (document, written in Erlang, used by BBC and Engine Yard)
• Dynomite (key-value, written in Erlang, used by Powerset)
• HBase (key-value, written in Java, used by Bing)
• Hypertable (tabular, written in C++, used by Baidu)
• Kai (key-value, written in Erlang)
• MemcacheDB (key-value, written in C, used by Reddit)
• MongoDB (document, written in C++, used by Electronic Arts, Github, NY Times and Sourceforge)
• Neo4j (graph, written in Java, used by some Swedish universities)
• Project Voldemort (key-value, written in Java, used by LinkedIn)
• Redis (key-value, written in C, used by Craigslist, Engine Yard and Github)
• Riak (key-value, written in Erlang, used by Comcast and Mochi Media)
• Ringo (key-value, written in Erlang, used by Nokia)
• Scalaris (key-value, written in Erlang, used by OnScale)
• Terrastore (document, written in Java)
• ThruDB (document, written in C++, used by JunkDepot.com)
• Tokyo Cabinet/Tokyo Tyrant (key-value, written in C, used by Mixi.jp (Japanese social networking site))

db4o (database for objects) is an embeddable open source object database for Java and .NET developers. It is developed, commercially licensed and supported by Versant.

db4o is written in Java and .NET and provides the respective APIs. db4o can run on any operating system that supports Java or .NET. 

Task 19: Update/Insert procedure

                                                            

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateInsert]
AS
SET NOCOUNT ON; 
BEGIN TRY
BEGIN TRANSACTION
DECLARE @ERROR_MESSAGE NVARCHAR(100) = ERROR_MESSAGE();
DECLARE @ERROR_SEVERITY INT = ERROR_SEVERITY();


UPDATE ZIP
SET 
ZIP.[ZipCode] = ZC.[PostalCode],
ZIP.[CityID] = C.[ID],
ZIP.[StateID] = S.[ID],
ZIP.[County] = ZC.[County]
FROM dbo.ZipCode ZIP
INNER JOIN [New].dbo.tblZipCode ZC ON ZIP.[ZipCode] = ZC.[PostalCode]
LEFT JOIN [QA].dbo.[city] c on c.name = zc.city
LEFT JOIN [QA].dbo.[state] s on s.name = zc.[State]
--------------------------------------------------------------

SET IDENTITY_INSERT dbo.ZipCode ON
INSERT INTO dbo.ZipCode(
[ID],
[ZipCode],
[CityID],
[StateID],
[County]
)
SELECT 
ZC.[PostalCodeID],
ZC.[PostalCode],
C.[ID] AS CityID,
S.[ID] AS StateID,
ZC.[County]
FROM dbo.ZipCode ZIP
RIGHT JOIN [New].dbo.tblZipCode ZC ON ZIP.[ZipCode] = ZC.[PostalCode]
LEFT JOIN [QA].dbo.[city] c on c.name = zc.city
LEFT JOIN [QA].dbo.[state] s on s.name = zc.[State]
WHERE ZIP.[ID] is null

SET IDENTITY_INSERT dbo.ZipCode OFF

--SELECT * FROM [New].dbo.tblZipCode
--SELECT * FROM [QA].dbo.ZipCode

--------------------------------------------------------------
UPDATE ZIP
SET 
ZIP.[CityID] = C.[ID],
ZIP.[StateID] = S.[ID]
 
FROM dbo.ZipCode ZIP
INNER JOIN [DieselDirect_New].dbo.tblZipCode ZC ON ZIP.[ZipCode] = ZC.[PostalCode]
LEFT JOIN [DDBillingPortal_QA].dbo.[city] c on c.name = zc.city
LEFT JOIN [DDBillingPortal_QA].dbo.[state] s on s.name = zc.[State]

COMMIT TRAN -- Transaction Success!

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
   RAISERROR(@ERROR_MESSAGE,@ERROR_SEVERITY, 1)
END CATCH

Task 18: SQL Profiler

     SQL Profiler is the graphical tool that lets you capture and analyzer SQL Server events in real time. I'm going to drill a bit deeper into this useful tool and show you an interesting book by Brad McGehee. You can download it from here.

• Chapter 01: Getting Started with Profiler
• Chapter 02: Working with Traces and Templates
• Chapter 03: Profiler GUI Tips and Tricks
• Chapter 04: How to Identify Slow Running Queries
• Chapter 05: How to Identify and Troubleshoot SQL Server Problems
• Chapter 06: Using Profiler to Audit Database Activity
• Chapter 07: Using Profiler with the Database Engine Tuning Advisor
• Chapter 08: Correlating Profiler with Performance Monitor
• Chapter 09: How to Capture Profiler Traces Programmatically
• Chapter 10: Profiler Best Practices
• Chapter 11: Profiler Events and Data Columns Explained

You should also check SQL Profiler Tips and Tricks and SQL Profiler Best Practices

Keyboard Shortcuts

• Ctrl+Shift+Delete to clear the current trace window
• Ctrl+F to open the Find dialog box
• F3 to find the next match
• Shift+F3 to find the previous match
• Ctrl+N to open a new trace
• F5 to start a replay
• Shift+F5 to stop a replay
• F9 to toggle a breakpoint
• F10 to single-step
• Ctrl+F10 to run to the cursor

A BETTER SQL SERVER PROFILER ALTERNATIVE is accuprofiler  - http://www.accuprofiler.com/

Task 17: SSIS with variable excel connection manager

1. In order to deploy an Excel Source with .xlsx extension you need to install the following provider:
http://www.microsoft.com/en-us/download/details.aspx?id=13255

2. Create a new SSIS project (Integration Services Project1)

3. Create a new package (LoadCostChanges.dtsx)

4. Declare 3 variables: UserID, FilePath and VendorUploadID (the variables will be sent as parameters from a stored procedure to the package)



5. Add from toolbox a DataFlow component (Import the Excel File) and an Execute SQL Task.

6. Because the excel file can have multiple empty rows we'll need to delete them using an Execute SQL Task (but this will be the last step for the implementation)

7.  In Data Flow component choose an Excel Source, a Derived Column and an OLE DB Destination component from Toolbox.

8. Excel Source configuration:

9. Excel Connection Manger configuration

10.  Derived Column configuration

11. OLE DB Destination configuration

12. Excel Connection Manager configuration
Connection String:  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::FilePath] + ";Extended Properties=\"EXCEL 12.0;HDR=YES\";"

13. The dbo.LoadExcel must be created with all the columns from all the excel files if there are columns which can be mapped in more than one Excel Source. 

CREATE TABLE [dbo].[LoadExcel](

[LoadExcelID] [bigint] IDENTITY(1,1) NOT NULL,

[VendorUploadID] [int] NULL,

        ............................................

        [UserID] [uniqueidentifier] NULL,

 CONSTRAINT [PK_LoadExcel] PRIMARY KEY CLUSTERED 

(

[LoadExcelID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

14. You need to connect to your integration server and deploy all your packages in SQL Server:

15. The Stored procedure which call the packages and sent them parameters : 

USE [TFMVendorPortal]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-----------------------------------------------------------------

--settings before running for the first time xp_cmdshell command

-----------------------------------------------------------------

/*

sp_configure 'Show Advanced Options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

EXEC sp_configure 'show advanced options', 1 

GO

RECONFIGURE WITH OVERRIDE 

GO

EXEC sp_configure 'xp_cmdshell', 1 

GO

RECONFIGURE WITH OVERRIDE 

GO

EXEC sp_configure 'show advanced options', 0 

GO

*/

ALTER PROCEDURE [dbo].[tfm_RunSSISPackages] 

@VendorUploadId int  ,@FilePath varchar(500), @UserID UNIQUEIDENTIFIER

AS

BEGIN

DECLARE @SQL NVARCHAR(max)

 ----TEST SCRIPT 

--DECLARE @SQL NVARCHAR(MAX)

--declare @FilePath nvarchar(max)

--declare @VendorUploadID INT

--SET @VendorUploadId = 233

--set @FilePath = '\\192.168.1.3\tfm\7\vernon_CostChanges_2012-10-15_17-13-33-718.xlsx'

set @SQL = '

EXEC master..xp_cmdshell ''""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /DTS "\File System\LoadCostChanges" /SERVER "SFVM05" /set \package.variables[User::VendorUploadID].Value;"'+ cast(@VendorUploadID as nvarchar(max))+ '" /set \package.variables[User::FilePath].Value;"'+ @FilePath + '""'''

exec (@SQL)

END

Task 16. BCP utility

Bulk Copy Program is a tool used for copying data between servers, especially when you don't have a Business Intelligence Development Studio installed on the server from where you want to took the data.
You can find more about other methods of doing this here.

The task was to copy the databases size records from one server to another one in order to process the data. To obtain the databases size from one server I used the following query:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
where database_id >4 --skip system databases
GO

or you can use:

with fs

as (
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

The steps are:
1. Create a .bat file to run the bcp command using one of the mentioned queries.
2. Go to D:\ and create a New Text Document named run_bcp.bat .
3.Open your .bat file using Notepad/Notepad++ and write down the following commands:

rem D:\bcp_outputQuery.csv
bcp "SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files where database_id > 4" queryout "D:\bcp_outputQuery.csv" -c -t"," -r"\n" -S <<Server IP>> -T

4.Using a Command Prompt window go to your .bat file and run it using  .\run_bcp.bat.  It will start copying the data in your CSV file.
5. Copy the CSV file on your server and export data using an SSIS package ( from .csv file to a table)


If you can connect remotely to the server where the data needs to arrive then it's more easy. You create a login on the server where data comes from with full rights and then using SSIS you'll be able to connect to the server.

Task 15: Delete Duplicate records

      Methods to remove duplicate records in SQL Server:

1. When we receive more than one rows of records, we need to remove the row which is not the first one and we can do this like below:


/* Delete Duplicate records */
WITH CTE (col1,col2,col3, DuplicateCount) 
AS 
(
SELECT col1,col2,col3, ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1) AS DuplicateCount
FROM table_name 
)
DELETE FROM CTE 
WHERE DuplicateCount > 1

Task 14: Pagination

I’m sharing a pagination method that I used in my scripts with a Common Table Expression and the RowNumber function and this seems to be the more elegant method to display a number of lines per page. You can integrate the CTE in a stored procedure or add it as a dynamic script.

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

;With CTEPage

AS

(

SELECT 

ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW,

PageId, 

PageName 

FROM PaginationTable

) 

SELECT PageId, PageName FROM CTEPage

WHERE RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

GO

The PageSize and PageIndex can also be added as parameters in a stored procedure:

CREATE PROCEDURE [dbo].[Report_pagination]
   @PageSize INT = 10, @Page INT =1
AS
SET NOCOUNT ON;
CREATE TABLE #temp ( ...)

;WITH PageNumbers AS
(
SELECT 
  columns,
          ROW_NUMBER() OVER (ORDER BY col_name) AS rownum

FROM #temp
 LEFT JOIN table_name
)

SELECT  *
FROM    PageNumbers
WHERE   rownum  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)
ORDER BY col_name