SQL Server

Getting Started With SQL Server Reporting Services (SSRS) – Part 4


Overview

Hi Friends! This is fourth article of the series Getting Started With SQL Server Reporting Services (SSRS). In previous three articles we learned following

  • How to create Simple Report using SQL Server Reporting Services?
  • How to show data in Table Control from database?
  • How to create parameterized reports?
  • How to set visibility for report controls?
  • How to show chart in reports?

For those who have not read previous articles they can read it from these links :

Getting Started With SQL Server Reporting Services (SSRS) – Part 1
Getting Started With SQL Server Reporting Services (SSRS) – Part 2
Getting Started With SQL Server Reporting Services (SSRS) – Part 3

Now, Some times we need to display data of multiple report into one single report. To achieve this instead of creating complete new report, all datasets we can use SubReport Control of SSRS to add multiple sub reports in a main single report. In this article we will learn how to use SubReport Control in our report to reduce our designing and development work. I hope if you are reading all articles of this series then you are almost used to with my Report Project 1 Solution.

So let’s start with our previous Report Server Project 1 Solution.
(more…)

Advertisements

How to Shrink SQL Server Transaction Logs


Back up your database first.

Launch SQL Server Management Studio. Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)

Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:

DBCC SHRINKFILE(<log_file_name_Log>)
BACKUP LOG <database> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log_file_name_Log>)

A little more information :

The ‘proper’ thing to do these days is to put the database into ‘simple recovery’ and then to shrink the log.

A few commands that I think might do it :

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>) 
ALTER DATABASE [mydatabase] SET RECOVERY FULL

Some notes on SQL Server backups :

  • transaction logs have to be backed up in order for them to ‘truncate’. If they are not backed up regularly, they will eventually fill the disk
  • a full backup alone will not do the trick – that does not touch the transaction log
  • transaction logs must be backed up via ‘BACKUP LOG’
  • if the dba does not require transaction log backups, it is advisable to move to Simple Recovery

6 Things You Should Know About SQL Server 2016 Always On Encryption


Always_Encrypted-graphic-e1432801397251The first public preview of SQL Server 2016 is now available for download. It is the biggest leap forward in Microsoft’s data platform history with real-time operational analytics, rich visualizations on mobile devices, built-in advanced analytics, new advanced security technology, and new hybrid cloud scenarios. SQL Server 2016 release promises many new features including a “Stretch” feature which allows you to automatically archive older data to the cloud, enhanced in-memory OLTP functionality, and several new enhancements in security.  One of the most interesting new security features is Always On Encryption.  Here are 6 things you should know about this feature.

  1. Data is encrypted at all times
    Okay, so this might seem obvious but lets look at what this really means.  In the diagram above you see that the data for one or more columns of a table is stored in an encrypted state.  When SQL Server acts on this data locally it acts only on the encrypted version.  It never decrypts it and so it’s encrypted in memory as well as on the wire as it transits the network (or Internet) on the way to the client.  SQL Server treats the encrypted data as if it were the raw field.  Only at the point where the data reaches the client is it decrypted for use in your applications.  This makes the encrypted data nearly impervious to man-in-the-middle attacks or file based decryption on the server.
    (more…)

Difference between Stored Procedure and Function in SQL Server


imagesThis article will take you closer to Stored Procedures and functions. Some of you may be already familiar with these two most commonly used terms in SQL Server. This article will explain difference between Stored Procedures and functions. Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called.

Basic Difference

  1. Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
  3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

  1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
  2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
  5. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
  6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
  7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
  8. We can go for Transaction Management in Procedure whereas we can’t go in Function.
Summary

In this article I try to explain the difference between Stored Procedure and Function. I hope after reading this article you will be able to understand BASIC as well as ADVANCE difference between Stored Procedure and Functions (UDF). Comments, criticism and suggestions are always welcome.

SQL Create Backup


This topic describes how to create a full database backup in SQL Server 2016 by using Transact-SQL.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2012';
GO

SQL – Basic Syntax


SQL is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax:

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

Important point to be noted is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements, but MySQL makes difference in table names. So if you are working with MySQL, then you need to give table names as they exist in the database.

SQL SELECT Statement:

SELECT column1, column2....columnN
FROM   table_name;

SQL DISTINCT Clause:

SELECT DISTINCT column1, column2....columnN
FROM   table_name;

SQL WHERE Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION;

SQL AND/OR Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION-1 {AND|OR} CONDITION-2;

(more…)

INDEX Constraint in SQL


The INDEX is used to create and retrieve data from the database very quickly. Index can be created by using single or group of columns in a table. When index is created, it is assigned a ROWID for each row before it sorts out the data.

Proper indexes are good for performance in large databases, but you need to be careful while creating index. Selection of fields depends on what you are using in your SQL queries.

Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns:

CREATE TABLE CUSTOMERS(
       ID   INT              NOT NULL,
       NAME VARCHAR (20)     NOT NULL,
       AGE  INT              NOT NULL,
       ADDRESS  CHAR (25) ,
       SALARY   DECIMAL (18, 2),       
       PRIMARY KEY (ID)
);

Now, you can create index on single or multiple columns using the following syntax:

CREATE INDEX index_name
    ON table_name ( column1, column2.....);

To create an INDEX on AGE column, to optimize the search on customers for a particular age, following is the SQL syntax:

CREATE INDEX idx_age
    ON CUSTOMERS ( AGE );

DROP an INDEX Constraint:

To drop an INDEX constraint, use the following SQL:

ALTER TABLE CUSTOMERS
   DROP INDEX idx_age;