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.