vote up
0
vote down

SQL SERVER – Check Advanced Server Configuration

I was recently asked following question: “How I check all the advanced configuration of the SQL Server?” EXEC sp_configure 'Show Advanced Options', 1; GO RECONFIGURE; GO EXEC sp_configure; Running above script will return all the advanced
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jul 29 2010 06:04 PM
vote up
0
vote down

SQLAuthority News – 2 Sessions at TechInsight 2010 – June 29 – July 1, 2010

Earlier this month, I got the opportunity to visit Malaysia for community sessions on June 29 – July 1, 2010 at Kuala Lumpur, Malaysia, which I would consider as valuable experience. I presented two different sessions at the event. The event was
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: dba mvp pinal dave sql
Jul 28 2010 06:06 PM
vote up
0
vote down

SQL SERVER – Computed Column – Perisisted and Storage

Every time I go for SQL Server Training or Consultation, I always learn something from my attendees. Sometime I even learn that a simple concept which we think is easy and whole world knows, many times very few knows about the same. In recent training I
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql data storage
Jul 27 2010 11:28 PM
vote up
0
vote down

SQL SERVER – FIX: ERROR: 8170 Insufficient result space to convert uniqueidentifier value to char

I just came across very simple error and the solution was even simpler. While concatenating NEWID to another varchar string, I had to CONVERT/CAST it to VARCHAR and I accidentally put length of VARCHAR to 10 instead of 36. It displayed following error.
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql error messages
Jul 26 2010 04:43 PM
vote up
0
vote down

SQLAuthority News – Last 2 Day to Win MSDN Subscription – Total 2 to Win

Today is the last day to win MSDN subscription on this blog. SQL Server MVP Madhivanan is known name. As there are more than 150 comments, I had requested him to help me out with deciding the winner. After looking at the quality responses, he has for
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: mvp pinal dave sql sql authority
Jul 25 2010 06:16 PM
vote up
0
vote down

SQL SERVER – Find Queries using Parallelism from Cached Plan

I recently came across wonderful blog post of Feodor Georgiev. He is one fine developer and like to dwell in the subject of performance tuning and query optimizations. He is one real genius and original blogger. Recently I came across his wonderful
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: best practices pinal dave sql sql authority
Jul 24 2010 04:30 PM
vote up
0
vote down

SQL SERVER – SELECT * FROM dual – Dual Equivalent

This blog post is for all the Oracle developers who keep on asking for the lack of “dual” table in SQL Server. Here is a quick note about DUAL table, in an easy question-and-answer format. What is DUAL in Oracle? Dual is a table that is
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jul 20 2010 06:14 PM
vote up
0
vote down

SQL SERVER – Identifying Statistics Used by Query

“Can I know which statistics were used by my query?” I was recently asked this question in my recent training of the class of query optimization and performance tuning. I really like the question. The answer for me is very simple.
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql optimization
Jul 19 2010 05:39 PM
vote up
0
vote down

SQLAuthority News – Community TechDays, Ahmedabad – July 24, 2010

Dive deep into the world of Microsoft technologies at the Community TechDays and get trained on the latest from Microsoft. Build real connections with Microsoft experts and community members, and gain the inspiration and skills needed to maximize your
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jul 17 2010 06:26 PM
vote up
0
vote down

SQL SERVER – Datetime Function TODATETIMEOFFSET Example

Earlier I wrote about SQL SERVER – Datetime Function SWITCHOFFSET Example. After reading this blog post, I got another quick reply that if I can explain the usage of TODATETIMEOFFSET as well. Let us go over the definition of the TODATETIMEOFFSET from
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql datetime
Jul 16 2010 05:35 PM
vote up
0
vote down

SQL SERVER – Datetime Function SWITCHOFFSET Example

I was recently asked if I know how SWITCHOFFSET works. This feature only works in SQL Server 2008. Here is quick definition of the same from BOL: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql datetime
Jul 15 2010 04:42 PM
vote up
0
vote down

SQLAuthority News – Two SQL Sessions at SQL Data Camp at Chennai – July 17, 2010

I will be presenting two SQL Server advance level sessions at SQL Data Camp @ Chennai. I am very excited for this event as I am going to meet my friends Sugesh, Deepak, Vidhya and Madhivanan at this event. All of them are SQL Server MVPs. I have come to
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: mvp pinal dave sql sql authority
Jul 15 2010 05:38 AM
vote up
0
vote down

SQLAuthority News – Two SQL Sessions at SQL Data Camp at Chennai – July 17, 2010

I will be presenting two SQL Server advance level sessions at SQL Data Camp @ Chennai. I am very excited for this event as I am going to meet my friends Sugesh, Deepak, Vidhya and Madhivanan at this event. All of them are SQL Server MVPs. I have come to
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: mvp pinal dave sql sql authority
Jul 14 2010 05:06 PM
vote up
0
vote down

SQL SERVER – How do I Learn and How do I Teach

This blog post is written in response to T-SQL Tuesday hosted by Robert L Davis (aka SQLSoldier). The blog post has raised three very interesting questions. How do you learn? How do you teach? What are you learning or teaching? Let me try to answer the
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: about me pinal dave sql sql authority
Jul 13 2010 06:16 PM
vote up
0
vote down

SQL SERVER – Parallelism – Row per Processor – Row per Thread – Thread 0

Earlier I had posted article answering question. “When SQL Server executes any query on multiple processors, do all processors process equal numbers of rows?” Read the answer over SQL SERVER – Parallelism – Row per Processor – Row per Thread.
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave readers contribution readers question sql
Jul 11 2010 05:04 PM
vote up
0
vote down

SQLAuthority News – Milestone – 1400th Post and Why do I blog

I am very glad today that I have reached milestone of 1400th post. I was looking back to my journey which I started on Nov 1, 2006 and I feel that it has been long way. I have noticed a lot of changes in myself too. Earlier, I used to write a milestone
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: about me pinal dave sql sql authority
Jul 10 2010 06:20 PM
vote up
0
vote down

SQLAuthority News – I am a MVP and I Love SQL Server

I am very glad that I received this prestigious award for the third time in a row. I am very thankful to Microsoft for introducing this wonderful technology of SQL Server. I enjoy getting involved with the community, which also helps in my
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: about me mvp pinal dave sql
Jul 09 2010 05:22 PM
vote up
0
vote down

SQL SERVER – The Self Join – Inner Join and Outer Join

Self Join has always been an note-worthy case. It is interesting to ask questions on self join in a room full of developers. I often ask – if there are three kind of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave readers question sql sql authority
Jul 08 2010 06:23 PM
vote up
0
vote down

SQL SERVER – Upper Case Shortcut SQL Server Management Studio

Few days ago, I received code which is very similar to code shown below. select * from Sales.SalesOrderDetail where ProductID > 777 I am not the guy who go crazy for formatting but I do appreciate proper coding. I like if the code was formatted like
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jul 07 2010 07:26 PM
vote up
0
vote down

SQLAuthority News – I am Microsoft Certified Trainer (MCT)

I am a Microsoft Certified Trainer and I am very much proud of it. Because I am a MCT, I have the support of great community leaders and trainers who help me constantly to improve in what I do. I have many Microsoft Certifications and I constantly try to
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: about me pinal dave sql sql authority
Jul 06 2010 10:43 PM
vote up
0
vote down

SQL SERVER – PowerShell Version Info

I have multiple computer systems at home. I have previously taken a picture of my home office and published it here. Also, I recently had a scenario where I was listing a PowerShell version installed in my computer systems. While searching online, I
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jul 06 2010 12:55 AM
vote up
0
vote down

SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats

In the recent Query Tuning project, one of the developers who were helping me out in the project asked me if there is any way that he could know how many pages are used by any Index, and if there is any way I could demonstrate the different levels of
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: dmv pinal dave sql sql authority
Jul 04 2010 04:30 PM
vote up
0
vote down

SQL SERVER – View XML Query Plans in SSMS as Graphical Execution Plan

Earlier I wrote a blog post on SQL SERVER – Parallelism – Row per Processor – Row per Thread, where I mentioned the XML Plan. As a follow up on the blog post, I received the request to send the same execution plan so that the blog readers can also use
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jul 03 2010 05:14 AM
vote up
0
vote down

SQL SERVER – Parallelism – Row per Processor – Row per Thread

Here is a question I received via email: “When SQL Server executes any query on multiple processors, do all processors process equal numbers of rows?” I find this one very interesting. I quickly wrote down a query which can run on multiple
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jul 02 2010 04:20 AM
vote up
0
vote down

SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts

Last month I wrote monthly round up and I was very well received. For the same here it goes this months wrote up for all the SQLAuthority.com blogs. The month started very interesting subject of SQL SERVER – Precision of SMALLDATETIME – A 1
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: about me pinal dave sql sql authority
Jun 30 2010 04:22 AM
vote up
0
vote down

SQL SERVER – Outer Join Not Allowed in Indexed Views

I recently received an email that contains a question from one of my readers. I have already replied the answer to his email, but I would still like to bring it to your attention and ask if you think I could have done any better with the example I gave.
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql index
Jun 29 2010 05:39 AM
vote up
0
vote down

SQLAuthority News – Exam 70-433 – MCTS – Microsoft SQL Server 2008, Database Development

I often receive lots of questions regarding how to pass SQL Server Certification exams. I have previously written about the road map over SQL SERVER – Roadmap of Microsoft Certifications – SQL Server Certifications. I have tremendous respect for
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: best practices mvp pinal dave sql
Jun 29 2010 04:00 AM
vote up
0
vote down

SQL SERVER – Default Statistics on Column – Automatic Statistics on Column

During the SQL Server Training, I frequently noticed confusion in people in terms of Statistics. Many people have no idea on how Statistics works. There are so many misconceptions with respect to Statistics. I recently had an interesting conversation
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql joins
Jun 28 2010 07:03 AM
vote up
0
vote down

SQL SERVER – Fast Track Data Warehouse for SQL Server 2008

I recently attended a wonderful training session organized by Microsoft on Fast Track Data Warehouse Reference Architectures. If you are regular reader of my blog, you will be well aware of the fact that I am more of the Relational guy than a Business
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: best practices business intelligence data warehousing pinal dave
Jun 26 2010 05:42 AM
vote up
0
vote down

SQLAuthority News – Download – Microsoft SQL Server 2008 R2 Best Practices Analyzer

Microsoft has released wonderful tool SQL Server 2008 R2 Best Practices Analyzer. I have previously used this tool and found it quite helpful. Here is the latest version which you can download from MS site. Microsoft SQL Server 2008 R2 Best Practices
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: best practices pinal dave sql sql add-on
Jun 25 2010 04:56 AM
vote up
0
vote down

SQLAuthority News – Meeting Bryan Oliver and Learning Wisdom of Life

During my most recent travel outside India, I was fortunate enough to meet Bryan Oliver. I have heard a lot about him but never had chance to meet him in person. Just like we all do for someone we never met before, I had already some preconceived notions
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: about me database pinal dave sql
Jun 24 2010 04:07 AM
vote up
0
vote down

SQLAuthority News – Guest Post – SELECT * FROM XML – Jacob Sebastian

One of the most common problem SQL Server developers face while dealing with XML is related to writing the correct XPath expression to read a specific value from an XML document. I usually get a lot of questions by email, on my blog or in the forums
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql query
Jun 23 2010 05:14 AM
vote up
0
vote down

SQL SERVER – TRANSACTION, DML and Schema Locks

Today we will be going over a simple but interesting concept. Many a time, I have come across the lack of understanding on how the transactions work in SQL Server. Today we will go over a small but interesting observation. One of my clients had recently
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority
Jun 21 2010 05:36 AM
vote up
0
vote down

SQLAuthority News – Delivering Two SQL Sessions at SQL Data Camp at Chennai – July 17, 2010

SQL Server Community is very strong community world-wide. In India SQL is considered as one of the most popular technology. Chennai is the only city in India where there are more than 3 SQL Server MVPs are from. My MVP friends has arranged one of the
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql index
Jun 19 2010 08:28 AM
vote up
0
vote down

SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache

This will drop all the clean buffers so we will be able to start again from there. Now, run the following script and check the execution plan of the query. Have you ever wondered what types of data are there in your cache? During SQL Server Trainings, I
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: dmv pinal dave sql sql authority
Jun 17 2010 06:18 AM
vote up
0
vote down

SQL SERVER – Find Largest Supported DML Operation – Question to You

SQL Server is very big and it is not possible to know everything in SQL Server but we all keep learning. Recently I was going over the best practices of transactions log and I come across following statement. The log size must be at least twice the size
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: best practices pinal dave readers contribution readers question
Jun 16 2010 05:12 AM
vote up
0
vote down

SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

Earlier I wrote blog post SQL SERVER – Difference Between GETDATE and SYSDATETIME which inspired me to write SQL SERVER – Difference Between DATETIME and DATETIME2. Now earlier two blog post inspired me to write this blog post (and 4 emails and 3
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql datetime
Jun 12 2010 05:53 AM
vote up
0
vote down

SQL SERVER – Difference Between DATETIME and DATETIME2

Yesterday I have written a very quick blog post on SQL SERVER – Difference Between GETDATE and SYSDATETIME and I got tremendous response for the same. I suggest you read that blog post before continuing this blog post today. I had asked people to
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql datetime
Jun 11 2010 05:12 AM
vote up
0
vote down

SQL SERVER – Difference Between GETDATE and SYSDATETIME

Sometime something so simple skips our mind. I never knew the difference between GETDATE and SYSDATETIME. I just ran simple query as following and realized the difference. SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime In case of GETDATE the
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave sql sql authority sql datetime
Jun 10 2010 06:16 AM
vote up
0
vote down

SQL SERVER – Fastest Way to Restore the Database

A few days ago, I received following email: “Pinal, We are in an emergency situation. We have a large database of around 80+ GB and its backup is of 50+ GB in size. We need to restore this database ASAP and use it; however, restoring the database
Journey to SQL Authority with Pinal Dave
 
pinaldave
tags: pinal dave readers question sql sql authority
Jun 09 2010 05:22 AM