SQL SERVER – GROUP BY Columns with XMLPATH – Comma Delimit Multiple Rows

This is one of the most popular question and I keep on getting again and again in email, Facebook and on social media. I have decided to write about it here in the blog so in future I can directly give a reference.

Here is the question – there is the question. There is a table with name of the student and their classid, now we have to create another table where we have different representation of the classid and student names. In simple words, we have to group by classid and concat user names. Here is how image representations of the same.

Here is the script of the original table which generates a table displayed on the left side of the image.

USE tempdb
GO
CREATE TABLE StudentEnrolled (ClassID INT, FirstName VARCHAR(20), LastName VARCHAR(20))
GO
INSERT INTO StudentEnrolled (ClassID, FirstName, LastName)
SELECT 1, 'Thomas', 'Callan'
UNION ALL
SELECT 1, 'Henry', 'Quinto'
UNION ALL
SELECT 2, 'Greg', 'McCarthy'
UNION ALL
SELECT 2, 'Brad', 'Grey'
UNION ALL
SELECT 2, 'Loren', 'Oliver'
UNION ALL
SELECT 3, 'Elliot', 'Kirkland'
GO
--
SELECT *
FROM StudentEnrolled
GO

Now we can use XMLPATH to concat the firstname and lastname of the student and along with that we can also group by them using following script. Now this was just an example, but you can in future use this script for many other purposes.

SELECT
[ClassID],
STUFF((
SELECT ', ' + [FirstName] + ' ' + [LastName]
FROM StudentEnrolled
WHERE (ClassID = SE.ClassID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS FullName
FROM StudentEnrolled SE
GROUP BY ClassID
GO

Let me know if there is any better way to do the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – ​Building Technical Reference Library – Notes from the Field #048

[Note from Pinal]: This is a 48th episode of Notes from the Field series. How do you build a technical reference library? In other word, when you need help how do you create your own reference so you do not have to go out to look for further help. There are so many little tips and tricks one should know and Brian Kelley has amazing skills to explain this simple concept with easy words.

In this episode of the Notes from the Field series database expert Brian Kelley explains a how to find out what has changed in deleted database. Read the experience of Brian in his own words.


Do you have a technical reference library? If you’re not sure what I mean, a technical reference library is your collection of notes, code, configuration options, bugs you’ve hit that you think you’ll hit again, and anything else that you might need to retrieve again in the future related to what you do in IT. If you have a technical reference library (hereafter referred to as TRL), is it:

  • outside of email?
  • distributed across multiple locations/computers?
  • searchable?
  • fast?

With my TRL, I’m more efficient because I‘m not searching the Internet again and again for the same information. I also can ensure I handle strange cases, such as unusual configurations, which we seem to get a lot of in IT. It’s in my TRL, so I don’t have to go back through a vendor’s install document or go run someone down in the organization to get the information I need. I already have it if I put it in my TRL. Because of the efficiency that TRLs provide, most top performing IT professionals that I know have some sort of system.

Outside of Email:

I used to have a folder in email where I kept technical reference documents. Because I try to follow Inbox Zero, I do have a Reference folder, but it’s not for technical documents. My Reference folder is typically related to what that mailbox is for. For instance, my LP Reference folder is for keeping procedures related to Linchpin such as how/where to enter time, who to contact about various things, etc.

Why don’t I have my technical documents in email any longer? Let me ask a question in response to that question: What happens when email is down? When email is down, you have no access to your TRL. Email does go down. I was faced with a case where I was responsible for getting email back up and, you guessed it, my technical notes were in email. That doesn’t work.

A second question to ask: How searchable is your TRL if it’s in email?  If you keep a lot of email, especially if you don’t have a specific folder for your TRL, searching may prove to be painful. That was the other problem I started to face.

Given these two issues, I advise building your TRL outside of email.

Distributed:

If your TRL  is only on a single computer, you’re going to regret it someday. That day usually occurs when the computer in question crashes and all your notes are lost. If you have a backup, anything you put into the library after the backup is gone. Give the prevelance of cloud-based solutions nowadays, having a technical reference library which is distributed is easy. Here are some ideas:

  • Evernote
  • Microsoft OneNote
  • Microsoft SkyDrive
  • DropBox
  • Google Docs
  • Apple iCloud

I’m particular to the first two, Evernote and OneNote, because they aren’t simply “file systems.” They are designed to capture and catalog information for quick retrieval later.

All my examples will come from Evernote, because that’s the application I typically use. In fact, here’s my setup. I have a specific notebook for my TRL:

TRL Notebook

If I know exactly what I’m looking for or if I’ve added it recently, I should be able to find any note quickly in the list of notes for the notebook:

Note: SQL 2012 Slipstream

Searchable (and Fast!):

Even if what I’m looking for isn’t right there at the top of the list, I can search in Evernote (and OneNote, if I was using it) to quickly locate the document. For instance, by typing “Slipstream,” I quickly get to the article that I want:

Search of TRL

Products live Evernote and OneNote have specifically worked on Search in order to retrieve results quickly. They also provide options to search within a notebook, for instance. In my case here, since slipstream is such a specialized term compared with what else is in my Evernote notebooks, I didn’t feel the need to filter by notebook. However, I could have if I recevied a lot of hits back or if the search was taking too long.

Also note that I’ve not added any tags to this article. I’m hitting it using a text search as to the contents alone. The use of tags offers another option in order to locate the material I need quickly. If I had a lot of articles that came up for a particular search word or phrase, I could look at using tags to differentiate them better. It’s another reason to consider tools designed to hold information and make it quickly retrievable.

Build a System That Works for You:

Learning expert Cynthia Tobias was once helping a teacher who asked her students to keep a reference notebook for assignments and handouts in class, an academic version of the TRL I’ve described thus far. The teacher balked at one student’s notebook because it was messy. The teacher couldn’t imagine how the student could locate anything in the notebook and was going to give the student a poor score. Tobias asked the teacher, “What’s the point?” The point, the teacher indicated, was to be able to retrieve an assignment or handout quickly. Tobias challenged the teacher to check to see if the student could retrieve quickly (within a minute, for instance). If the student could, the teacher should leave the student alone. If the student couldn’t, then work with the student to improve the reference system.

That’s what you want to do. You want to develop a reference system that’s efficient for you. I’ve given you a snapshot of what works for me. It may not work for you. That’s okay. Start with something. If you’re starting from scratch, I would recommend starting with Evernote or OneNote. Put some notes in that you’ll need again. See how well you can retrieve those notes, especially as the number of notes increases. Make tweaks as you have to for performance sake. Most of all, build your TRL and become a better professional.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Practical Use of Backup Encryption

 Backup is extremely important for any DBA. Think of any disaster and backup will come to rescue users in adverse situation. Similarly, it is very critical that we keep our backup safe as well. If your backup fall in the hands of bad people, it is quite possible that it will be misused and become serious data integrity issue. Well, in this blog post we will see a practical scenario where we will see how we can use Backup Encryption to improve security of the bakcup.

Feature description

Database Backup Encryption is a brand new and long expected feature that is available now in SQL Server 2014. You can create an encrypted backup file by specifying the encryption algorithm and the encryptor (either a Certificate or Asymmetric Key).

The ability to protect a backup file with the password has been existing for many years. If you use SQL Server for a long time, you might remember the WITH PASSWORD option for the BACKUP command. The option prevented unauthorized access to the backup file.

However this approach did not provide reliable protection. In that regard, Greg Robidoux noted on MSSQLTIPS: “Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data.

To protect a backup file, SQL Server 2008 introduced the transparent data encryption (TDE) feature. Thus, a database had to be transparently encrypted before backup. Therefore, start with SQL Server 2012 the PASSWORD and MEDIAPASSWORD parameters are not used while creating backups. Even so, data encryption and backup files encryption are two different scenarios.

In case a database is stored locally, there is no need to encrypt it before backup. Fortunately in SQL Server 2014 there are two independent processes. Along with data encryption it is possible to encrypt a backup file based on a certificate or an asynchronous key. Supported encryption algorithms are:

  • AES 128
  • AES 192
  • AES 256
  • Triple DES

Practical use

To illustrate above mentioned, I will create an encrypted backup of the Adventureworks database. Also, you can back up directly to Azure. If needed, you may restore the encrypted back up file on Azure.

I will use dbForge Studio for SQL Server to create the encrypted backup file.

To protect the backup file we need to create an encryptor: either a Certificate or Asymmetric Key. Then, we need to pass this encryptor to the target SQL Server to restore the backup. For this, the encryptor must be exported from the source SQL Server and imported to the target SQL Server. There are no problems with the certificate in this regard. It is more complicated with asymmetric keys.

Taking into account that the BACKUP ASYMMETRIC KEY command is not available, and we can not just create a duplicate for an asymmetric key (compared to symmetric key), the only approach is to create the asymmetric key outside the SQL Server. Then we can use the sn.exe utility to transfer it inside SQL Server (CREATE ASYMMETRIC KEYkeynameFROM FILE = ‘filename.snk‘). After that we can use this asymmetric key to encrypt the backup file on the source instance. Further we need to use the same *.snk file to create the asymmetric key on the target instance (and restore the backup file).

In our example we will not use asymmetric keys. We will use a certificate. Moreover the certificate (behind the scene) is the pair of open/closed keys.

Let’s create the server certificate and use it to encrypt the backup file.

The certificate will be protected with the database master key, because we didn’t specify the ENCRYPTION BY statement.

This is exactly what we need. Only certificates signed with the database master-key can be used for the encryption purposes. Otherwise, If we for instance, protect the certificate with the password ENCRYPTION BY PASSWORD = ‘strongpassword‘, the following error appears while attempting to encrypt the backup file:

“Cannot use certificate ‘CertName’, because its private key is not present or it is not protected by the database master key.”

Encrypted backups (along with usual backups) can be traditionally created locally on the hard drive and in Azure Storage.

Instead of writing tons of SQL code I will use the convenient dbForge Studio for SQL Server Back Up wizard. The wizard allows to create the database backup in several clicks.

Step 1: Setup the DB Connection and the backup file location.

Step2: Setup mediaset

Step 3: Select the encryption algorithm and certificate.

In case you don’t want to pay extra attention to transferring the backup file to the Windows Azure, you can backup directly to Azure.

After the script execution in the required container the blob (with the backup) appears.

In case you had already created a backup with the same name in the same container, you can get the following error: There is currently a lease on the blob and no lease ID was specified in the request.

Further, you can restore the back up file on the Windows Azure.

Summary: 

Obviously, it is a good practice to encrypt a backup file while transferring. This, for instance, allows to avoid data leak while transferring backups from one DPC to another.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – UPDATE From SELECT Statement with Condition

An email from an old college friend landed my mailbox:

Hey Pinal,”

I have two tables. I want to conditionally update data in one table based on another table. How can I do that. I have included sample scripts and an image for further explanation.

Thanks!”

It always delights to receive email from an old college friend and particularly it is even more interesting when they have a question w where I can help. Here is the question and a sample script.

User had two tables – ItemList and ItemPrice. The requirement was to update ItemPrice table column Price with US price and for that it required to divide the column by 60. Here is the sample script of the table displayed in the image.

USE tempdb;
GO
CREATE TABLE ItemList
(ID INT, ItemDesc VARCHAR(100), Country VARCHAR(100));
INSERT INTO ItemList (ID, ItemDesc, Country)
SELECT 1, 'Car', 'USA'
UNION ALL
SELECT 2, 'Phone', 'India'
UNION ALL
SELECT 3, 'Computer', 'USA';
GO
CREATE TABLE ItemPrice
(ID INT, Price VARCHAR(100));
INSERT INTO ItemPrice (ID, Price)
SELECT 1, 5000
UNION ALL
SELECT 2, 10000
UNION ALL
SELECT 3, 20000;
GO
-- SELECT Data
SELECT *
FROM ItemList;
SELECT *
FROM ItemPrice;

Now let us write a script which will update the table as per our expectation.

-- Update Statement
UPDATE ItemPrice
SET Price = Price/60
FROM ItemList il
INNER JOIN ItemPrice ip ON il.ID = ip.ID
WHERE Country = 'USA'
GO

Now let us result by selecting the data in our Price table.

Now you can see how we can update from table to another table with conditions. You can clean up above code by dropping tables.

-- Clean up
DROP TABLE ItemPrice;
DROP TABLE ItemList;
GO

I hope this quick script helps, let me know if there is any better alternative.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Check Snapshot Isolation State of Database

It is very easy to know the snapshot Isolation State of Database and here is the quick script for the same.

SELECT name
, s.snapshot_isolation_state
, snapshot_isolation_state_desc
, is_read_committed_snapshot_on
FROM sys.databases s

Upon running above code it will return the results describing the status of the isolation level for your database. Here is the screenshot which describes the same.

Just on a side note, remember that READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. Whereas, SNAPSHOT does optimistic reads and optimistic writes. It is recommended that you go for READ COMMITTED SNAPSHOT for most of your application where you want to implement row versioning. Microsoft has a detailed article on this subject over here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Server 2008 R2 Service Pack 3 – Download

It has been a long time since SQL Server 2008 R2 got Service Pack Update. Microsoft has finally released SQL Server 2008 R2 service pack  3and its feature pack. SQL Server 2008 R2 SP3 contains fixes to issues reported as well as Hotfix solutions have provided since SQL Server 2008 R2 Service Pack 2 up to and including Cumulative Update 13.

I have personally switched to SQL Server 2014 few months ago and I am happy with its performance and robust behavior. Many of the customer and clients are still using SQL Server 2012. However, if you are using SQL Server 2008 R2, I suggest that you look at upgrading to the latest version of SQL Server or at least update your software with latest service pack.

You can download SQL Server 2008 R2 Service Pack from following link:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Extension of the SQL Server Natively Compiled Stored Procedures

Earlier I wrote a blog post about the Location of Natively Compiled Stored Procedure and Naming Convention. In this blog post, I wrote about location of natively compiled stored procedures.

In the blog post, I have used following image.

One of the questions which I have received was what do various extensions like c, dllobj etc means. My friend Balmukund Lakhani explains that very well in the his blog post, however for the reference it is listed here once again.

File Extension Usage
.c C source file generated by In-Memory engine
.dll Natively compiled DLL to be loaded into SQL Server process
.mat.xml MAT export file
.obj Object file generated by C compiler
.out Compiler output file
.pdb Symbol file for the dll. Used for debugging code issues.

Reference: Pinal Dave (http://blog.sqlauthority.com)