SSRS (SQL Server Reporting Services)

SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.

Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and APIs that enable developers to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. You can publish reports, schedule report processing, or access reports on-demand. Reporting Services also enables you to create ad hoc reports based on predefined models, and to interactively explore data within the model. You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. Reporting Services provides the key to your business data.

SQL CMD Utility

What is XP_CMDSHELL ?

XP_CMDSHELL - extended stored procedure.
With this extended stored procedure you have the ability to run any command line process within your SQL script, so you can embed this within your stored procedures, jobs or batch processing.
In SQL Server 2000 this option is enabled by default, but to limit access to using xp_cmdshell only members of the sysadmin server role have default rights.
To extend rights to other users you can use the GRANT statement to allow execute rights.
With SQL Server 2005 the use of xp_cmdshell is turned off by default. As SQL Server continues to mature Microsoft has put many safeguards in place to minimize the security risks of unwanted code being executed within SQL Server or outside of SQL Server.

Examples of XP_SMDSHELL Commands

     1) XP_CMDSHELL Directory Search

For example. If the User dose not have access to the physical SQL Server machine, but if he want to see all the files in the “C:\temp” directory that have a “.sql” extension. He can do this by issuing the following command:

exec master.dbo.xp_cmdshell ‘dir c:\temp\*.sql’

    2) Create a new directory using XP_CMDSHELL
This is the simple example Imagine a user is in process of building a new SQL Agent job using Enterprise Manager. To organize the output from all the steps in this new job, he want the output of each step to go into a common directory on the server, where each step has its own output file. The only problem is the new directory does not exist. here we assume that used do not have access to the server, or do not want to walk down the hall to the computer room to create this new directory. No problem he can just enter the following command in Query Analyzer from the privacy of your office to accomplish creating the new directory:

exec master.dbo.xp_cmdshell ‘mkdir “c:\temp\SQL Agent Output\new_job\*


3) Run DTS package via stored procedure using XP_CMDSHELL
As you can see, it is very easy to execute DOS commands using xp_cmdshell. So can xp_cmdshell also run executables? The answer is YES! Suppose you have a DTS Package that you want to run via a stored procedure. There is no “sp_executeDTS” stored procedure, but you can use the following xp_cmdshell command to run your DTS package:

exec master.dbo.xp_cmdshell ‘dtsrun -E -Sserver1 -N”Export Invoices”‘

Note :This command executed the “dtsrun” executable with the necessary parameters to run the “Export Invoices” DTS package. Are you starting to see how the xp_cmdshell extented store procedure can provide you with some alternatives and also expands the functionality you can build into a T-SQL script.

CTE [Common Table Expression]

Overview : 
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:

• Create a recursive query.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE

---

Example :
A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

Types of License for SQL Server

Enterprise leverages the power of the Microsoft® SQL Server (MS-SQL) platform. Microsoft SQL Express is the native database management system for the network fax solution. Microsoft SQL Express enables customers to effortlessly make use of the product ‘out of the box’, while allowing database scalability through
seamless migration to MS-SQL Server if required to meet greater storage demands or to integrate with existing installations. 

       In cases where the capacity constraints inherent in SQL Express may be a limiting factor with regard to a customer’s unique needs, it is recommended that the licensed server edition, Microsoft SQL Server, be utilized. MS-SQL Server is based on product licensing, which can be arranged with a Certified Microsoft Product Reseller.
      There are several types of licensing models for MS-SQL Server. Each model addresses different computing environments, from a handful of users or seats, to multiple server processor models with a large number of users requiring MS-SQL access.
 

License Types

 
There are three MS-SQL license types, detailed as follows:

•  Server License: For the Server running MS-SQL
• Client Access License (CAL): For the user or device accessing MS-SQL
•  Processor License: Per physical or virtual processor being used to run the MS-SQL instance

Licensing Models

 

There are three MS-SQL licensing models where the abovementioned license types are used:

• Processor Based Licensing
• Server License + User CAL
• Server License + Device CAL

Process or Based Licensing
     Processor based licensing is based on the number of (physical or virtual) processors designated in an operating system to running MS-SQL Server. Obtaining the license for this model alleviates the need to license each individual device or user that will need to access MS-SQL. In short, this type of license enables an unlimited number of users and/or devices to access MS-SQL.
     For multi-core central processing units (CPU), licensing remains based on the number of CPUs, not on the number of CPU cores. Processor based licensing is available for Enterprise, Standard and Workgroup editions of MS-SQL. Limitations are placed on how many CPUs are supported by each SQL server version.

Server + User CALs
The CAL is similar to the legacy licensing model of most software. Licensing is based on the individual user of the software, or in the case of MS-SQL, the user who accesses the database. In addition to the individual user CAL, a server license must be purchased in addition to the total number of user CALs. This type of CAL grants user access to multiple MS-SQL Servers.
 

Server + Device CALs
As most devices became network aware, (for instance cell phones, PDAs, etc.), Microsoft created the Server + Device CALs licensing model. This licensing model is based on the Server + User CALs model; however the license in this case applies to a device, as opposed to a user. This type of license is most effective in cases where users share the same device (terminal or PC). In line with the Server + User CALs, the Server + Device CALs licensing model also requires a separate license for each MS-SQL server.

Entity Relationship Diagram

Definition: An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. ER diagrams often use symbols to represent three different types of information. Boxes are commonly used to represent entities. Diamonds are normally used to represent relationships and ovals are used to represent attributes.

Also Known As: ER Diagram, E-R Diagram, entity-relationship model

 

Examples:

Consider the example of a database that contains information on the residents of a city. The ER digram shown in the image above contains two entities -- people and cities. There is a single "Lives In" relationship. In our example, due to space constraints, there is only one attribute associated with each entity. People have names and cities have populations. In a real-world example, each one of these would likely have many different attributes.

Database Normalization

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

The Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't be discussed here.

Before we begin our discussion of the normal forms, it's important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it's extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let's explore the normal forms.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

• Meet all the requirements of the first normal form.
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

• Meet all the requirements of the second normal form.
• Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

• Meet all the requirements of the third normal form.
• A relation is in 4NF if it has no multi-valued dependencies.

Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

Top five Things Biggners Need to Know About Database

1. SQL Forms the Core of Relational Databases

You can't avoid it. The Structured Query Language forms the core of all relational databases. It provides a uniform interface to Oracle, SQL Server, Access and other relational databases and is a "must learn" for all aspiring database users. In fact, I encourage you to take an introductory SQL course before you even attempt to learn any specific database software. The investment of time will help you build a proper foundation and get started in the world of databases on the correct foot. For a quick start, read SQL Basics or, for a more comprehensive introduction,Refer About.com

2. Selecting Primary Keys is an Extremely Important Decision

The selection of a primary key is one of the most critical decisions you’ll make in the design of a new database. The most important constraint is that you must ensure that the selected key is unique. If it’s possible that two records (past, present, or future) may share the same value for an attribute, it’s a poor choice for a primary key. When evaluating this constraint, you should think creatively. You'll also need to avoid sensitive values, such as Social Security Numbers, as they raise privacy concerns. For more information on selecting a strong primary key, read Choosing a Primary Key.

3. NULL Is Not Zero or the Empty String

NULL is a very special value in the world of databases, but it's something that beginners often get confused about. When you see a NULL value, interpret it as "unknown". If a quantity is NULL, that doesn't necessarily mean that the quantity is zero. Similarly, if a text field holds a NULL value, that doesn't mean that there isn't an appropriate value, it's simply unknown. For example, consider a database containing information about children who attend a particular school. If the secretary entering the record does not know a student's age a NULL value is used to indicate the "unknown" placeholder. The student certainly has an age, it's just not present in the database.

4. Converting Spreadsheets to Databases Saves Time

If you already have tons of data stored in Excel (or other) spreadsheets, you can save yourself mountains of time by converting those spreadsheets into database tables. Read our tutorial on Converting Excel Spreadsheets to Access Databases to get started.

5. All Database Platforms Are NOT Created Equal

There are many different databases out there and all offer a variety of different features at different price points. Some are full-featured enterprise databases designed to host huge data warehouses serving multinational enterprises. Others are desktop databases better suited to tracking inventory for a small store with one or two users. Your business requirements will dictate the appropriate database platform for your needs.

Database Mirroring

Overview :

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.

Note :   We cannot mirror the master, msdb, tempdb, or model databases.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Benefits of Database Mirroring

---

Database mirroring benefits:

• Increases data protection.
  Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance. For more information, see "Operating Modes," later in this topic.
  A database mirroring partner running on SQL Server 2008 Enterprise or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error.
• Increases availability of a database.
  In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (without data loss). In the other operating modes, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database. For more information, see "Role Switching," later in this topic.
• Improves the availability of the production database during upgrades.
  To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are participating in a database mirroring session. This will incur the downtime of only a single failover. This form of upgrade is known as a rolling upgrade.

How Database Mirroring Works

---

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

Replication

SQL Replication term describes a group of technologies allowing information distribution and mirroring between different databases. SQL replication allows not only for copying data between databases, but also copying any database objects as well. Essentially replication performs synchronization between databases. By utilizing SQL replication, you can distribute data to as many remote network locations you need, and you can do that over different types of networks including LAN, WAN, and Internet to name a few.

Why use SQL Replication?

There are many reasons why SQL replication exists. In many scenarios replication is done to ensure redundancy. For example if a business runs a mission-critical database, it may be wise to have a replication of this database on a separate physical database server, which can take over in case of failure in the primary database (software corruption, hardware failure, etc.). Another valid reason to use SQL replication is load balancing. By using replication you can share the access load between several database server having identical databases. There are many other reason to use SQL replication, however we won’t discuss them here.

Replication Publisher and Subscriber

To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher.

MS SQL Server Replication

MS SQL Server has 3 main replication models – Merge Replication, Transactional Replication and Snapshot Replication.
The Merge replication is used when both Publisher and Subscriber need to make changes to their respective databases. In this case both databases might have been changed between runs of the Merge SQL replication, and the replication merges the changes in both locations. Of course when using merge replication you should be aware that there might be conflicts, for example duplicated primary keys. If there is a conflict, then the merge replication follows predetermined conflict resolution plan to correct the issue.
The Transactional replication is usually used with databases where data changes frequently and there’s need for constant refreshing of the data. The replication process watches the publisher’s database for any changes, if there are changes, it distributes them over to the replication subscribers.
The Snapshot replication is relatively rarely used, and as its name implies it does a snapshot of the publisher database at certain point of time and distributes it to the subscribers. The Snapshot replication is time and resource consuming compared to the other two SQL replication types and is used when you need to fully overwrite the database at the subscriber or when you do an initial database replication update, which will be refreshed using one of the other 2 SQL replication types after the initial copy.

Using the BETWEEN condition

Use the BETWEEN condition to display rows based on a range of values.

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;


The BETWEEN Condition
You can display rows based on a range of values using the BETWEEN range condition. The range that you
specify contains a lower limit and an upper limit.
The SELECT statement on the slide returns rows from the EMPLOYEES table for any employee whose
salary is between $2,500 and $3,500.
Values specified with the BETWEEN condition are inclusive. You must specify the lower limit first .

Interview Question for SQL

How do you implement one-to-one, one-to-many and many-to-many relationships while designingtables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

 

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default
primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.
Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

 

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the

primary key of the table. If the table has more than one candidate key, one of them will become the primary
key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for

Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

 

Explain different isolation levels

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL

Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of
isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for
an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets
you customize the isolation level at the connection level.

 

What's the maximum size of a row?

8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check
out SQL Server books online for the page titled: "Maximum Capacity Specifications".

 

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level
locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being
occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer
coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards
it's dynamically managed by SQL Server.

 

What are constraints? Explain different types of constraints.

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to
create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

 

What is a join and explain different types of joins?

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table
depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER
JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here
is an example:
Employees table which contains rows for normal employees as well as managers. So, to find out the managers
of all the employees, you need a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super
bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

 

Basis Syntax of SQL

SQL Select

  1) SELECT * from table_name
       This syntax is used for selecting all the data from the table or for selecting all table.
    
                   Example : Select * from Employee

                         This will select all the data from the table Employee

  2) SELECT column_name(s)  FROM table_name
       This syntax is used for selecting particular column from the table .

                  Example  : Select Firstname,Lastname  from Employee

                      This will select only Column name (Firstname and Lastname)
                  

SQL Distinct

         In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.The DISTINCT keyword can be used to return only distinct (different) values.

  1) SELECT DISTINCT column_name(s) FROM table_name

            Example  : Select  DISTINCT Lastname  from Employee

                      This will select only distinct lastname from employee table

SQL Where
         Where is used  for condition in writing query
              Select Column_name from table_name WHERE column_name
            Example  : Select Firstname  from Employee where Lastname = Parker 

SQL And & Or
        
SQL Order By
SQL Insert
SQL Update
SQL Delete

SQL Intro

SQL is a standard language for accessing and manipulating databases.

---

What is SQL?

• SQL stands for Structured Query Language and lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard

---

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

---

Using SQL in Your Web Site

To build a web site that shows some data from a database, you will need the following:

• An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
• A server-side scripting language, like PHP or ASP
• SQL
• HTML / CSS

---

RDBMS

RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collections of related data entries and it consists of columns and rows.
 _____________________________________________________________

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.

---

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:

• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index