In this article, I described how to pass a table to a function parameter in SQL Server. In this article, you create a table, student, then create a user-defined table type and pass the table type as a parameter to a function. So let's have a look at a practical example of how to pass a table as a function parameter in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Here is how to implement passing a user-defined table type to a function.

1. Create a Student Table in SQL Server
Create a table named Student.
CREATE TABLE [dbo].[Student] 
( 
    [StudentID] [int] NULL, 
    [StudentName] [varchar](30) NULL, 
    [StudentFees] [int] NULL 
) 

2. Create a User-Defined Table Type in SQL Server
Now create a user-defined table type to be used as a table-valued parameter in the function.
CREATE TYPE dbo.StudentType AS TABLE 
( 
   [StudentID] [int] , 
   [StudentName] [varchar](30) , 
   [StudentFees] [int]  
) 

Now Press F8 to see the created type in the Object Explorer.
Database->Programmability->Types->User Define Table Types

3. Creating a Function in SQL Server
Now create the StudentDetailFunctionFunction. This function will accept a table-valued parameter.
READONLY keyword - This keyword is required to declare a table-valued parameter.

ALTER FUNCTION StudentDetailFunction( @StudentDetail dbo.StudentType READONLY ) 
RETURNS VARCHAR(50) 
AS 
BEGIN 
    DECLARE @Studentname VARCHAR(50) 
    SELECT  @Studentname= StudentName FROM @StudentDetail 
    RETURN @Studentname 
END 

4. Execute the SQL Server Function
Now you can declare a variable @StudentVariable which contains the value of the table columns.
DECLARE @StudentVariable AS StudentType 
INSERT INTO @StudentVariable(StudentName) VALUES('Peter') 
SELECT dbo.StudentDetailFunction(@StudentVariable) 

Memory is one of the most-used resources in SQL Server. Generally, the more you have, the better query performance you’ll get. How can you track your server’s memory usage? One way is to use the Performance Monitor (Perfmon) counters exposed through the sys.dm_os_performance_counters DMV. One indicator of memory performance is Page Life Expectancy (PLE). You can capture basic memory usage over time by setting up a SQL Server Agent job to query this DMV, inserting the results into a table, and reporting on the table results.

COLLECTING THE DATA

I have a “DBAInfo” database on my instance that I use to track metrics and other information. I create a new table, MemoryHistory.

USE DBAInfo;
CREATE TABLE MemoryHistory
(ID INT IDENTITY NOT NULL,
CollectionDateTime DATETIME,
PerfmonObjectName NCHAR(128),
CounterName NCHAR(128),
CounterValue BIGINT)

Then, I create a new SQL Server Agent job that runs every 5 minutes.

The only step in this job is the below query, which queries the DMV and inserts the results into the table I created.

INSERT INTO MemoryHistory
SELECT CURRENT_TIMESTAMP,
object_name,
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager';

I schedule the job to run every five minutes.

VIEWING THE DATA

Now, this data isn’t going to do me any good unless I view it, and make a decision or perform an action based on what I learn.

To view the data I’ve collected, I run the following query:

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory;

That’s a lot of junk to sort through when all I want to see is PLE, so I narrow down the query a bit.

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory
WHERE CounterName = 'Page life expectancy';

But who wants to read through results like that each time there’s a problem to see when PLE rose or fell? Not me. I’d rather see it in a graphical format. How can I do that?

SQL Server Reporting Services

I have SSRS at my disposal. I’m going to create a very simple report that will allow me to enter start and end dates, and will display a line chart for PLE during that time.

REPORTING ON THE DATA

I set up my report to have DBAInfo as my data source. In order to choose dates, I use the following query as my dataset.

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory
WHERE CounterName = 'Page life expectancy'
AND CONVERT(DATE, CollectionDateTime) >= @Start
AND CONVERT(DATE, CollectionDateTime) <= @End;

I change my @Start and @End parameters to “Date/Time” so I get a date picker.

I drag a Line Chart onto the design surface and add the CounterValue as my Value and CollectionDateTime as my Category Group.

I can preview the report to view it:

Last but not least, I’ll deploy this report to Report Manager so that I and others can run it, or even schedule a regular subscription.

It has traditionally been almost impossible to architect infrastructure-class, highly available (HA) SQL Server solutions using shared storage in the public cloud. Recently Microsoft announced support for some System Center 2012 SP1 applications to work with SQL AlwaysOn, a new way to achieve HA SQL. AlwaysOn uses an availability group concept, much like Exchange 2010 database availability groups (DAGs) to achieve clustered HA services without shared storage.

This is good news for architects looking to move management workloads into the public cloud when appropriate. In theory, a pair of powerful VMs in Azure running SQL 2012 AlwaysOn can approach and exceed the HA SLAs expected of many mission critical applications. Figure 1 shows the new dashboard view of AlwaysOn HA availability groups with some System Center 2012 SP1 databases made highly available.

Figure 1

HA SQL is Important. What is the Reason?

Highly available (HA) SQL Server services are the cornerstone of many enterprise database applications. Few enterprise solutions today are deployed on non-HA SQL. Without an HA mechanism for database services, enterprise applications and e-commerce websites cannot offer maximum available uptime. Even perfectly managed servers require periodic restarts for updates and maintenance; and you always need to be prepared for equipment failure such as extended outages of particular servers or disk drives.

The traditional way to offer HA SQL is by creating a SQL Server failover cluster based on shared storage. That is, a storage area network (SAN) presents shelves of disk drives to two or more servers at the same time ("shared storage"). The SAN and shared storage is often the most expensive component in the datacenter. Public cloud solutions abstract you from the storage, and usually don't offer the kind of infrastructure you would need to run a conventional HA SQL failover cluster with shared storage in the cloud.

SQL Server 2012 AlwaysOn, no need for SAN

With SQL Server 2012 AlwaysOn, two or more complete copies of each HA database can exist, synchronized by AlwaysOn technology. The independent database copies are presumed to exist locally in direct attached storage (DAS) on each SQL node, or over on the network using economical Windows Server 2012 SMB 3.0 file shares on dedicated Windows Server 2012 file servers.

- You can use Windows Server 2012 Standard for the SQL server nodes-unlike in previous Windows releases, you can enable the failover cluster feature in the Standard edition of Windows Server 2012 as well as Windows Server 2012 Datacenter.

- SQL Server 2012 does require the Enterprise edition of SQL Server 2012 to use the AlwaysOn feature.

Steps to deploy a SQL Server 2012 AlwaysOn Availability Group

Here are the high-level steps to follow to deploy SQL 2012 AlwaysOn in a SQL 2012 failover cluster (without shared storage).

1. Install Windows Server 2012 in two computers or virtual machines (VMs), each with a single network interface card (NIC).

2. Create a two-node failover cluster without shared storage. You will need a cluster name and an IP address for the cluster network name.

3. Install SQL Server 2012 Enterprise on both computers as if they were going to be stand-alone SQL servers.

• When you install, use a domain account for the SQL server services.
• Open the Windows Firewall on ports TCP 1433 and TCP 5022.

4. Create a temporary "seed" database on the first SQL server using SQL Server 2012 Management Studio. This database will be used to establish the AlwaysOn cluster, and then can be deleted after the first production database is deployed.

5. Make sure the database is of the "Full" type model, and perform a SQL Backup job.

6. In the Management Studio, create an AlwaysOn Availability Group and an Availability Group Listener. (The Availability Group Listener is essentially the virtual (or clustered) SQL Server instance. There is a one-to-one relationship between availability groups and listeners.)

• Assign a DNS name and TCP IP address for the AlwaysOn Availability Group and an Availability Group Listener.
• Assign a shared network folder that is accessible to all SQL servers that will have AlwaysOn database replicas.

7. At the AlwaysOn High Availability node, right-click and select Add A Database To An Availability Group. If your database is of the Full type and has been backed up, the status will be "Meets Requirements". Click Next.

8. Select that you will perform a full synchronization, using the shared network folder you specified in step 6(b). Click Next.

9. Enter security information to access the primary database replica. Click Next, observe the validation and click Next, and then Finish.

10. Observe after a moment that new database replica on the secondary node in the SQL AlwaysOn availability group has been created, as seen in Figure 2.

Figure 2

In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below

And this is Data into table

I want output in following format

                                  Department                               FirstName
                                   IT                                             amiT,Emp1,Emp5
                                  Admin                                       Shobhit, Emp3,Emp7

and so on

To get this desired result we need to write below mentioned query

1SELECT DISTINCT
2Department,
3EmpNames = substring( ( SELECT ', ' + FirstName
4FROM Employees e2
5WHERE e2.Department = e1.Department FOR XML path(''), elements
6),2,500)
7FROM Employees e1

And the output of this SQL Query would be

In this article, we will see how to do Database Backup Compression in SQL Server 2012. To do that you can use SQL Server Management Studio and also you can use a Transact-SQL statement. So let's have a look at a practical example of how to do database backup with compression. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

To visually create a Database Backup

Now Press F8 to open the Object Browser in SQL Server Management Studio and expend it.

Select database then right-click on the selected database then click on "Task" then click on "Back Up".

This will open the following window:

To specify the destination of the database, select the destination then click on the Add Button. We then see a new dialog box, click on the browse button to choose the location

Now next click on "Options" and the following screen appears:

Now select Compress Backup.

The above image contains three options to select:

1. The first option, "Use the default server settings" tells the backup to use the server's default backup compression setting. By default, it is set to have backup compression off.
2. The Second option "Compress Backup" turns backup compression on.
3. The "Do not compress backup" option turns it off.

Now click again on the "General" option.

Now you will see that the database backup has completed successfully.

Here are some important points to consider while restoring a database backup. Let us first create a sample database named test using the following code.

A new database named test will be created in your server and data and log files are created in the d: drive.

Let us assume that you regularly take backup of this database using the below code:

backup database test to disk='d:\test.bak'

Now if you want to create another database or restore this test database to a new database named testing, you can use the following code

restore database testing from disk='d:\test.bak'

However executing the code above will give you the following error

Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_dat.mdf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_dat' cannot be restored to 'h:\test_dat.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_log.ldf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_log' cannot be restored to 'h:\test_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

This error occurs because the data and log files are currently being used by the database test. So you need to give different names for those files while restoring, as shown below

restore database testing from disk='d:\test.bak'
with
move 'test_dat' to 'd:\testing.mdf',
move 'test_log' to 'd:\testing.ldf'

The above code will work fine and new database will be created with the name testing.

Just be aware of this point while restoring a backup of existing database!

As you may know, you cannot detach a mirrored database or bring it offline to move a data or log file from one drive to another drive. Moving a database file for a mirrored database is not the same as moving a normal database. Here I will show you the step by step process on how to move the data and/or log file(s) from one drive to another drive with minimum downtime.

Solution

Moving database files can be done two ways; by detaching the database then moving the database file(s) to the target location and then attaching the database from the new location. The other option is to run an ALTER statement to change the file location in the system catalog view, bring the database offline, then copy the file(s) to the target location and bring the database online. With database mirroring enabled for the database, both options will fail because your database is mirrored. We can't detach the mirrored database, nor can we bring it OFFLINE.

Here is step by step solution to reduce your downtime and move your database file from one location to another location for a mirrored database.

Steps

Step 1
Check the database files location for all database files.  Here we are going to move database "NASSP2".

sp_helpdb NASSP2

Here we can see two database files placed on the C: drive. As per best practice, we should not place database files on the system C: drive, so this is what we are going to move.

Step 2
Check the database mirroring configuration for your database. Run the below script to check the database mirroring status and its current partner name.

SELECT (SELECT DB_NAME(7)AS DBName),
database_id,
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name,
mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=7

We can see the mirroring_role_desc for this server is principal and its partner/mirrored instance name.

Step 3
If the database file size is big it will take some time to copy from one drive to another drive. So to over come this issue and minimize the downtime, we will failover our database from our principal server to its MIRROR server and route our application to the new principal server (earlier mirrored box) to bring the application online and run business as usual. Run the below command to failover this database.

ALTER DATABASE NASSP2 SET PARTNER FAILOVER

Step 4
Now we can again check the database mirroring configuration to see the current mirroring state. Run the same script which we ran in step 2. This time the output is the same, except one column. Here mirroring_state_desc is MIRROR where earlier it was principal.

Now our principal instance has become mirrored. Ask your application team to change the ODBC configurations and route the application connection to the new principal server. Now we can do make changes without downtime being of any concern. Note that if you have databases in a shared environment, then you may need to failover all databases to the mirrored server to reduce any downtime.  The technique requires stopping the database services, so this could impact other databases on this server.

Step 5
As we saw in step 1, two database files are on the C: drive. Now we have to move these two database files from 'C:' to 'E\MSSQL2008\DATA' drive. First we need to run an ALTER DATABASE statement to change the file location in master database system catalog view. Make sure to run this ALTER statement for every file that needs to be moved to the new location.

ALTER DATABASE NASSP2
MODIFY FILE (NAME='NASSP2_System_Data', FILENAME='E:\MSSQL2008\DATA\nassp2_system_data.mdf')
go
ALTER DATABASE NASSP2
MODIFY FILE (NAME='NASSP2_log', FILENAME='E:\MSSQL2008\DATA\nassp2_log.ldf')
go

Step 6
Now, stop the SQL Server instance to copy the data and log file(s) to the target location. I used PowerShell to stop and start the services. You can use services.msc utility or SQL Server Configuration Manager as well.

STOP-SERVICE MSSQLSERVER –FORCE

Check the status of SQL Server service.

GET-SERVICE MSSQLSERVER

Step 7
Now copy both database files (nassp2_system_data.mdf and nassp2_log.ldf) to the new target location ('C' to 'E:\MSSQL2008\DATA').

Step 8
Once both files has been copied, start the SQL Server services.

START-SERVICE MSSQLSERVER

Check the status of SQL Server service

GET-SERVICE MSSQLSERVER

Step 9
Once SQL Server has started, failback your database from current principal to your primary box. Run step 1 again to check the location of the files and run step 2 again to check the mirroring status.

This article going to talk about real world query optimization.Most of the times all developers and Database administrators face the long time running query.so this article will help to you optimize the sql query with index.

I've run a simple test on sql query involve 2 tables, tblEmail & tblEmailPromotion

Table columns: 

tblEmail (email varchar(255), IsDeleted int)
tblEmailPromotion (email varchar(255), PromotionID int)

Both tables without index,

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID
= 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 2:43 (2 minutes 43 seconds) to get the result.

Change the question from "LEFT OUTER JOIN" to "IN" become

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE
pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

Now, you’ll see the different, it will takes faster result.

Now, we index table tblEmail column Email (Unique)

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID
= 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 1:22 (1 minute 22 seconds) to get the result

Now, we run the 2nd query:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE
pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It only takes 27 Seconds

To make the 2nd query better:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6 AND pe.Email LIKE '%hotmail%')
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

This will give 15 seconds.Now you can feel the different way of query optimization and retrive records from database in short time.

Hope the tutorial above help you. If you’re looking for SQL 2012 hosting on Europe server, please visit HostForLIFE.eu. HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.microsoft.com/web/hosting/HostingProvider/Details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.

In this article, we make a database in Microsoft Access and that database is used in SQL Server. 

So let's take a look at a practical example of how to import a Microsoft Access database table into SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple steps to do that as described here.

Creating a Empty Database in SQL Server

Now Press F8 to open the Object Browser in SQL Server Management Studio and expend it.

Database -> right-click-> select New database

This would open the "New database" window:

Now enter a database name to create a database.

Now click on the OK button to create the database. The new database will be shown in the Object Explorer:

Creating a Table in Access Database

Now open the Microsoft Access database and create a database.  The Access database looks as in the following:

Importing Access database in SQL Server

Now right-click on the Test database and select the Import Data option.

database Name -> Tasks -> Import data

Now click on Import data. The import and export wizard will be opened.

Now select a Data Source. And browse the file from the system.

Now click on the Next Button and enter the computer name or server name and select a database.

Server Name: MCNDESKTOP08\MYMSSQLSERVER

Database name: Test

Now click on the Next Button.

Now click on the Next Button.

Now again click on the Next Button.

Now click on the Finish Button.

And finally click on the Close button.

All of your Access data is nicely imported into a SQL Server 2008 database. Now refresh the test database and expand it to see the imported table. 

Sometimes you’ll get this error message when queries your database:

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation"

So, how to fix this error?

Just use the following syntax to collate on the fly when joining up tables with different collations. I integrate system so have to do this allot.

select * from [Product] p join [category] c
on 
c.[Name] collate SQL_Latin1_General_CP1_CI_AS
=
p.[Name] collate SQL_Latin1_General_CP1_CI_AS

Hope this help.