[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
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…
Read MoreSQL SERVER – Performance: Do-it-Yourself Caching with Memcached vs. Automated Caching with 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…
Read MoreMySQL – Pattern Matching Comparison Using Regular Expressions with REGEXP
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…
Read MoreSQL SERVER – What is the Difference Between Latches and Locks
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…
Read MoreSQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running
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,…
Read MoreSQL SERVER – Error – Resolution – Could not allocate space for object in database because the filegroup is full
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…
Read MoreSQL SERVER – SSIS Data Flow Troubleshooting – Part1 – Notes from the Field #019
[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…
Read MoreMySQL – How to do Straight Join in 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…
Read More
SQL Contest – Win Amazon Gift Cards – Learn How to Get Started with ClustrixDB
It has been a long time since we contest where we can learn something new and win something cool. I reached out to the good folks of Clustrix with the request to help me to build a contest where readers can learn and explore new technology, while stand a good…
Read More