About an year ago, I had posted SQL Server related Interview Questions and Answers. It was very well received in community. I have received many comments, suggestions and emails on this subject. I am planning to upgrade the Interview Questions and Answers and take it to next level.
Here, I need your help. Please your comments, [...]
Regular readers of my blog will be aware of my friend who called me few days ago with very a funny SQL Problem SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results. This time, it did not take long before he called me up with another interesting problem, although the issue he was [...]Today, we are going to discuss about something very simple, but quite commonly confused two options of ALTER DATABASE.
The first one is ALTER DATABASE …ROLLBACK IMMEDIATE and the second one is WITH NO_WAIT.
Many people think they are the same or are not sure of the difference between these two options. Before we continue our explaination, [...]Just a day ago, I was invited at Round Table meeting at prestigious organization. They were planning to implement High Availability solution using Database Mirroring. During the meeting, I have made few notes of what was being discussed there. I just thought it would be interested for all of you know about it.
Database Mirroring works [...]
This is very simple and known tip. Query Hint MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single [...]
In February 2008, Microsoft announced a record-breaking data load using Microsoft SQL Server Integration Services (SSIS): 1 TB of data in less than 30 minutes. That data load, using SQL Server Integration Services, was 30% faster than the previous best time using a commercial ETL tool. This paper outlines what it took: the software, hardware, [...]SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL [...]Download the SQL Server JDBC Driver 3.0 CTP, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5.
In its continued commitment to interoperability, Microsoft has released a preview of the upcoming Java Database Connectivity (JDBC) driver. The SQL Server JDBC Driver [...]I am recently working on upgrading my class Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning with additional details and more interesting examples. While working on slide deck I realized that I need to have one solid slide which talks about checklist for analyzing slow running queries. A quick search on my saved [...]
Recently I received the following two questions from readers and both the questions have very similar answers.
Question 1: I have a unique requirement where I do not want to use any index of the table; how can I achieve this?
Question 2: Currently my table uses clustered index and does seek operation; how can I convert [...]
Professional SQL Server 2008 Internals and Troubleshooting
by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Steven Wort
Link to Amazon (Worldwide)
Link to Flipkart (India)
Brief Review: Having a book on internal and associating that with real life is “almost” an impossible task. The reason for using the word “almost” is because this book has accomplished this [...]
This blog post is in the response of the T-SQL Tuesday #004: IO by Mike Walsh. The subject of this month is IO. Here is my quick blog post on how Cover Index can Improve Performance by Reducing IO.
Let us kick off this post with disclaimers about Index. Index is a very complex subject and [...]I was recently asked question about SQL Server 2008 pricing. I have bookmarked official site here which lists the pricing.
Official site: What’s New in SQL Server 2008 R2 Editions
Editions
Per Processor PricingRetail
Per Server Plus CAL PricingRetail
Parallel Data Warehouse
$57,498
Not offered via Server CAL
Datacenter
$57,498
Not offered via Server CAL
Enterprise
$28,749
$13,969 with 25 CALs
Standard
$7,499
$1,849 with 5 CALs
However, I have [...]
PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within the application users already know and love—Microsoft Excel. Office 2010 is the next version of Office 2010. We all know Office 2010 is on the verge of getting released and the reviews available online say that it’s a phenomenal product.
My [...]SQL Server Modeling (formerly code name “Oslo”) is a set of future technologies that provide significant productivity gains across the lifecycle of .NET applications by enabling developers, architects, and IT professionals to work together more effectively with SQL Server at the center of the application lifecycle.
SQL Server Modeling CTP Nov 2009 Release 2 is a [...]I recently received few comments that I have not written on simple subjects recently. In fact, this blog is dedicated to all those who are really learning SQL Server and almost all the articles and posts are posted here keeping this goal in mind. One of the questions in the email which requested to write [...]
This is very common concept that truncate can not be rolled back. I always hear conversation between developer if truncate can be rolled back or not.
If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from [...]
Recently I wrote about SQL SERVER – INSERT TOP (N) INTO Table – Using Top with INSERT I mentioned about how TOP works with INSERT. I have mentioned that I will write about the performance in next article. Here is the performance comparison of the two options.
It is clear that performance of the Insert TOP (N) [...].jpg)
TechEd Sri Lanka was held at Waters Edge, Colombo between Feb 8 and Feb 10, 2010. It was one of the largest successful technical event in Sri Lanka. I was extremely surprised to how technically sound this event was and how excited the TechEd attendees were.
I presented there on two different subject. They were very [...]
The performance of SQL Server is primarily decided by the disk I/O efficiency. Improving I/O definitely improves the performance. SQL Server 2008 introduced Data and Backup compression features to improve the disk I/O. Here, I will explain Data compression.
Data compression implies the reduction in the disk space reserved by data. Therefore, data compression can be [...]
I recently presented in Hyderabad User Group on the subject of The Other Side of SQL Server Index: Advanced Solutions to Ancient Problem , you can read more about this event here SQLAuthority News – MUGH – Microsoft User Group Hyderabad – Feb 2, 2010 Session Review. I really had great time talking about Index [...]
During my recent training at one of the clients, I was asked regarding the enhancement in TOP clause. When I demonstrated my script regarding how TOP works along with INSERT, one of the attendees suggested that I should also write about this script on my blog. Let me share this with all of you and [...]
Last year during my visit to SQLAuthority News – SQL PASS Summit, Seattle 2009 – Day 2 I have received ducks from the event. Well during the same event I had learned from Jonathan Kehayias the saying of ‘Keeping Your Ducks in a Row‘. The most popular theory suggests that “ducks in a row” came [...].jpg)
Earlier this month, I was very fortunate to visit Microsoft User Group Hyderabad lead by Hima Vindu Vejella. Hima is a very enthusiastic leader and kind person. I had a wonderful time meeting her as well her husband during my visit to Hyderabad. I had presented session on Index, which was well received.
Brief information on [...]
In this article we will go over basic understanding of Rollup clause in SQL Server. ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. Let us understand how it works by using an example.
Consider a table with the following structure and data:
CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT
)
GO
INSERT INTO tblPopulation VALUES('India', 'Delhi','East Delhi',9 [...]