My learnings from the sql share video I knew that we cannot use where clause with aggregate functions but I didn't know the reason. The reason is the where clause is executed before the aggregate functions are applied and hence cannot use the where clause with. Count(*) and count(columnname) may yield different results. This is because Count(*) does not ignore null values and count(columnname) ignores or does not count null values. When you do aggregation using the AVG function using the isnull ...
Read More
Yesterday I stumbled across this site while looking for something else. I couldn't find who the author is for this peice of work. This page contains a very comprehensive list of resources for SQL server developers and DBAs. Here is a snapshot of the table of contents from thsi site. Table of Contents Training: Labs Training: Podcasts Training: Tutorials Training: Videos Training: Webcasts Training: Whitepapers General Skills Skills: SQL Skills: Windows Scripting SQL Server SQL Server 2008 R2 SQL...
For those of you who don't know about SQL Share here is a snippet from thei About Us page. SQLShare is a division of Fourdeuce Inc, an Orlando, FL based company that also owns End to End Training, a well known provider of technical training. Fourdeuce is owned by Andy Warren, Brian Knight, and Steve Jones. I have been getting daily emails from SQL Share since my subscription in April 2010. I used to watch some of the videos. These are small videos that cover various aspects of SQL SERVER by vari...
Today featured video in SQL Share was Brian Kelly's "Using Login Auditing in SQL SERVER". I watched this 3 min. video and learnt the following. I know that we can view sql server error logs. I monitor them on a daily basis but I didnt know that the login audit requires a setup that is a bit similar to the normal windows event log set up. This can be set up when you right click on the sql server connection in the management studio and choose properties and then choose Security. There is n option ...
What is Sargability? -- I planned to attend Rob Farley's 24HOP session but could not make it. I didn't know what Sargability meant and thought I will google it. And here is what I have found out thanks to the internet. Here is a blog post which gives the history of how the word Sargable came into existence back in 2005 where Brad McGhee explains that the sargability is a real word but it is used a lot in the SQL Server world. So what exactly does Sargable mean? SARGABLE is a contraction for S ea...
Today I stumbled across this very useful post by Mladen Prajdić Click here to read more Haven't had a chance to try any of them yet, but this seems like a good list that can be in DBA's goodies hat up the sleeve.
Here are my takeaways from today's fantastic session of Adam The first basic point was you can use "." instead of "localhost" to choose the localhost server. Defining the attribute relationship was very clearly explained that the relatiosnhps should be from the lowest level to the highest level. Proactive caching -- available in from SQL SERver 2008 but the refresh property is available in Excel 2007 but sometimes may not work properly. The following two resources which are quite useful was also...
I managed to attend the SQL Pass session -- Relational Database Design for Utter Newbies by Kevin Kline. He covered the following topics • How to approach a new database design project • How to normalize a relational database up to Third Normal Form (3NF) • How to read an Entity-Relationship Diagram (ERD) or create your own • How to transform a design into a real database One thing that I liked about this presentation was the Mnemonic he mentioned -- I swear by the key, the whole key, and nothin...
Just a while ago I was watching a video on sqlshare Creating a Plan Guide for a Parameterized Statement By Andy Warren . Only then I heard about plan guides. I didn't know what plan guides were. Here is my find from googling In SQL Server 2005, there is a new feature called Plan Guides that can help out in some cases where you discover poorly performing queries that you don't have direct control over. Essentially, a Plan Guide allows you to add or modify query hints to queries on the fly, just b...