All Articles - SQL Authority with Pinal Dave

MySQL – Search For Values Within A Comma Separated Values – FIND_IN_SET

March 21, 2014

MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. It basically returns the index position of the first parameter within the second parameter. This can be alternatively used to replace the IN clause. Let us create the following tables CREATE TABLE items(item_id…

SQL SERVER – Index Fragmentation Next Steps – Notes from the Field #020

March 20, 2014

Pinal Dave

SQL

Notes from the Field, SQL Index, SQL Server

[Notes from Pinal]: Indexes are considered as a silver bullet for performance tuning and do amazing job when they are properly created. Just like any other engine they need maintenance and tuning. As we get more data, indexes start to get fragmented and performance starts to degrade. I asked direct questions to my friend Tim about index fragmentation and he gave me a very interesting answer.

SQL SERVER – Retrieve Last Inserted Rows from Table – Question with No Answer

March 19, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

MySQL

Some conversations are very interesting and I would like to bring them to everyone’s attention. Here is one of the conversation happened earlier this week. Let us say the question was asked by Dustin. Dustin: Can we retrieve last 10 rows from a table? Pinal: Sure, just get the top…

SQL SERVER – Performance: Do-it-Yourself Caching with Memcached vs. Automated Caching with SafePeak

March 18, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Safepeak

We’ve all been reading a lot of articles that suggest adding a caching layer in front of a database is more efficient than hitting the database directly. Among different caching options there is Do-It-Yourself coding, using open source tools like Memcached, or using Velocity (part of the Microsoft AppFabric) or…

MySQL – Pattern Matching Comparison Using Regular Expressions with REGEXP

March 17, 2014

Pinal Dave

SQL, SQL Tips and Tricks

MySQL

MySQL supports pattern matching comparison using regular expressions with REGEXP keyword. In this post we will see how to use REGEXP in MySQL. We can use the patterns ^, $, | and sqaure braces to effectively match the values. Let us create the following table: CREATE TABLE items(item_id INT, item_description…

SQL SERVER – What is the Difference Between Latches and Locks

March 16, 2014

Pinal Dave

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

A very common question I often get in the email is “What is the difference between latches and locks?” The answer to this question is not an easy one. Here is a quick answer from the MS white paper. A page in SQL Server is 8KB and can store multiple…

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

March 15, 2014

Pinal Dave

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

SQL Server Management Studio, SQL Stored Procedure

Here is one of the very common interview questions I often ask people – Q: What is the default Port SQL Server running on? A: 1433 for TCP/IP and 1434 for USD connection. I always get the correct answer for this question. However, when I ask a follow up question,…

SQL SERVER – Error – Resolution – Could not allocate space for object in database because the filegroup is full

March 14, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages

Just a day ago on my local box I received following error. Could not allocate space for object ‘<object_name>’ in database ‘<database_name>’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on…

SQL SERVER – SSIS Data Flow Troubleshooting – Part1 – Notes from the Field #019

March 13, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Notes from the Field, SQL Backup and Restore, SSIS

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications. The tool may also be used…

MySQL – How to do Straight Join in MySQL?

March 12, 2014

Pinal Dave

SQL, SQL Tips and Tricks

MySQL

In an earlier post, we have learnt a new type of join called NATURAL JOIN supported in MySQL. In this post will see another type of join called STRAIGHT_JOIN. STRAIGHT_JOIN is actually an INNER JOIN but it forces the order in which data should be processed. As per MySQL’s documentation…