DaveWentzel.com            All Things Data

SQL Server

Cool Uses For Windowing (Ranking) Functions

This is my first in a series of blog posts on window functions in SQL Server.  You are probably most familiar with ROW_NUMBER() OVER().  These are new features as of SQL 2005.  

The SQL:2003 specification now standardizes windowing functions.  Even Oracle and PostgresSQL have implemented them.  Window functions are defined as aggregates computed over a window.  Wow, that's helpful.  "An epistemologist is someone who studies epistemology."  Windowing can be thought of as an aggregation without the result set being filtered where the aggregated data is "mixed in" with the query result set.  Sometimes a few examples can drive the point home.  In the next few posts in this series I'll cover some really cool uses for windowing functions.  

SQL Server Data Files

I think I've proved earlier (as have others on the net) that autogrowth of data files is *not* your friend.  However, that seems to beg the question...then are more data files better than fewer data files, and if so, what's the magic formula?  

Data Virtualization

For some reason "data virtualization" is one of the new hot buzzwords in my industry.  Data virtualization is really just combining a bunch of data sources (databases, unstructured data, DWs, weblogs, whatever) into a single virtual data tier layer that provides access to applications.  Haven't we been doing this for years.  I remember when linked servers came out for SQL Server.  I thought they were the coolest thing at the time.  I could now connect to Oracle from my SQL Server and manipulate the data in the more familiar TSQL, realtime, without the need of an ETL process.

RAID 0+1 and RAID 1+0

I often hear people advocating the use of RAID 1+0 as the best RAID choice for a SQL data file.  In reality, these people really mean RAID 0+1, it's a simple mistake to make, but it does make you sound a little less-than-knowledgeable when you make the mistake (and I've made it often).  

SQL Injection

By now you should know all about SQL injection, I won't rehash it here.  I'm always interested in nifty tricks to overcome difficult problems.  For SQL Injection attacks where you are building dynamic sql most folks will tell you to use sp_executesql to overcome most of the headaches, vs simply taking a string parameter and passing it to EXEC().  But here's another trick I like that uses derived tables instead.

Tags: 

Some research on Solid State Drives

I started to do some research on SSD's and began to write a page on the subject.  

What is a Data Architect?

 

What is a Data Architect?

Ever since 2002my title has been data architect.  It's actually funny how this came about...my company had a rule that all "DBAs" had to reside under a particular department head, but my future manager, who was persuingme for awhile to join his team, worked in a different department.  To join the team I needed a different title ...hence my becoming a "data architect".  In an instant I was promoted...samejob, same duties, different title.  At the timeI didn't give much thought to the distinction in titles.  Andto this day I couldn't care less what my title is. 

Performance Problem Diagnosis Methodology

Performance Problem Diagnosis Methodology I'm often asked after solving some long-standing, difficult performance problem that couldn't be solved what methodology I use to diagnose and solve problems. My answer usually varies depending on the problem just solved, but that's not really the correct answer to the problem. The cop-out answer is simply "experience and a lot of study." The methodology I use is a lot like how a doctor diagnoses an illness...one of the reasons I love watching the show House on Fox.

SAN Controller Notes

 

Controller Cache
So, your SAN has a data cache and generally they aren't huge.  A few GB is standard.  And your SAN is hooked up to many different servers/applications.  So, what is the best controller cache configuration for (your) SQL Server? 

Pages

Subscribe to RSS - SQL Server