SQL Schema

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects

The use of schemas can help to combine entities from separate applications or functional areas, or logical groups, into a single physical database.

One example of relevance is the protection of database objects in application development environments where developers and testers share the same set of database objects

Table 'Crawler_Status' in the database CMS can be used by two different users as follows
Table1, CMS.Testing.Crawler_Status is used by testing team
Table2 , CMS.Production.Crawler_Status is used by production team

Note : Usually we use two different databases

Performance

Always refer to the database objects(Table names , Views) using a fully qualified name. At the very least, use the schema name followed by the object name, separated by a period (.)

Example
SELECT COL1 , COL2 FROM DBO.TABLE_NAME

It will take less processing time for the Database Engine to resolve object names
Otherwise database engine checks in master database,if it’s not there in master database it checks in the current session database depends on schema

Security

Use schemas to protect the base database object from being altered or removed from the database schema by users without sysadmin permissions.
User permissions should be managed at the schema level

Bulk Import

Points to remember when Bulk-Loading Data

We use different Import mechanisms (BCP , Bulk Insert or SSIS) to bulk-load data , we should consider the following factors to get top performance

1.Disable indexes and Bulk-load data
The best scenario is bulk- loading data into an empty heap ( a table with no indexes )

Disable indexes before Bulk-load and rebuild indexes once the Bulk-Load completed.

2.Bulk-logged Recovery Model
Change recovery mode from FULL to Bulk-Logged lets database perform minimal logging during the data load.

Data that is loaded during a bulk load usually has no need for the point-in-time recovery capability of the full recovery model.

Decreasing the volume of log writes improves performance.

Note: Recovery models - Full [Default Recovery Model] ,
Bulk Logged and
Simple

SQL Server 2008 Features - Data Tracking Solutions

Data Tracking Solutions

SQL Server 2008 introduces two tracking features that enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.

1.Change Data capture
2.Change Tracking

Before these features were available, custom tracking mechanisms had to be implemented in applications.

Elimiates expensive techniques

Using Triggers,timestamp columns, new tables to store tracking information, and custom cleanup processes.

Build a Data Tracking Solution by Change Data capture

Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed

Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system

We Can provide answers to a variety of key questions

What are ALL the changes that happened between 12:00AM and 12:00PM?

I want to get a transactional consistent set of all changes that happened in Order table and Order_Detail since 12:00PM

Which column changed?

Was the change an insert or an update?

Implementation

CDC enabled at two levels

Database Level - SP_CDC_ENABLE_DB
Individual tables - SP_CDC_ENABLE_TABLE


USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO


The following CDC tables are created under the CDC schema,

cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping

Synonyms

A synonym is an alternate name for a schema scoped object. Synonyms allow client applications to use the single-part name of a synonym to reference a base object instead of a using a two-, three-, or four-part name to reference the base object.

Advantage

It replaces long multi-part object name in SQL Statement

It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

Example


-- CREATE SYNONYMS
CREATE SYNONYM MyWork
FOR SQLWorks.dbo.test

-- USE SYNONYMS
SELECT * FROM MyWork

-- DROP SYNONYMS
DROP SYNONYM MyWork

Ranking Functions #1

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used.Ranking functions are nondeterministic.*

The four ranking functions are ROW_NUMBER(), NTILE(), RANK() and DENSE_RANK() in SQL.

#1 ROW_NUMBER() function

“Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.”
Syntax

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )

[ partition_by_clause ] is optional

partition_by_clause
Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied.
order_by_clause
Determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Example

--********** RANKING FUNCTION #1 ROW_NUMBER() ********
-- Create table
CREATE TABLE RankTest (CandidateID int NOT NULL,
[Year] smallint NOT NULL,
Department VARCHAR(100) NOT NULL
);

-- Insert data
INSERT INTO RankTest (CandidateID, [Year], Department)
select 1,2010,'SOFTWARE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'ECOMMERCE' UNION ALL
select 1,2010,'BIZINFO' UNION ALL select 1,2010,'SOFTWARE' UNION ALL
select 2,2010,'SOFTWARE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'ECOMMERCE' UNION ALL
select 2,2010,'BIZINFO' UNION ALL select 2,2010,'SOFTWARE';

--ROW_NUMBER() #1
SELECT CandidateID ,[Year] , Department , ROW_NUMBER() OVER (PARTITION BY Department ORDER BY CandidateID)
FROM RankTest

--ROW_NUMBER() #2
SELECT CandidateID ,[Year] , Department , ROW_NUMBER() OVER (ORDER BY Department)
FROM RankTest

NEWID() Function

Creates a unique value of type uniqueidentifier.

We can use this function to randomly sort records in the table


Example

--***** NEWID() *********

SELECT TOP 10 * FROM wages ORDER BY NEWID()

You will get new set records everytime you run the above query

CUBE Operator in TSQL

We can use CUBE operator with GROUP BY clause to summarize data in a table .

Following are the specific differences between CUBE and ROLLUP:

* CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
* ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end

Example:
--********* CUBE Operator ***********
SELECT
CASE WHEN GROUPING(CategoryName) = 1 THEN 'ALL Categories' ELSE CategoryName END AS Category_Name ,
CASE WHEN GROUPING(VENDOR) = 1 THEN 'ALL Vendors' ELSE VENDOR END AS Vendor, COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
WITH CUBE