SQL Server Nation
For all of your SQL Server needs.

Tip of the Week

Sort by: Most Recent | Most Viewed | Most Commented

  • Quickly Generate a Number Sequence in SQL Server

    If you do not currently have a numbers table setup in your database (which you should - they're invaluable), you quickly and easily generate a large list of sequential values using some crafty SQL statements and an internal SQL Server table. I'll be using the undocumented spt_values table in the master database. This table holds a potpourri of different database values that can be used for different reasons...and it also holds a small series of sequential numbers. I can use these sequential...

  • SQL Server Triggers

    Handling trigger is a very simple, and very misunderstood facet of SQL Server programming. Today I'll take a look some very common trigger scenarios, some mistakes made, and how you can program around them. First, a little bit of background of the types of triggers available in SQL Server. Trigger Types There are two different types of SQL Server triggers to date: INSTEAD OF triggers and AFTER triggers. INSTEAD OF triggers are placed on tables and views (most often views) and are invoked INSTEAD...

  • What version of SQL Server am I running?

    Quite often I see the question in the SQL Server forums "What version of SQL Server am I running?" To be honest, this is a VERY easy question to answer. Microsoft provides a function with a set of properties you can query for identifying such information. The function provided by Microsoft is SERVERPROPERTY. However, there are specific parameters you need to pass to it to get the information you need. So, rather than having to worry about these specific parameter values, I've created...

  • Why is this query slow: 1

    Since this is the first part of this series, I'll give a little background as to what I want to accomplish. At some point, you're going to run into a situation where a query on your SQL Server system is "slow". Slowness is usually ambiguous, and almost always relates to a persons reference. However, when we hear that something is slow or broken we need to check it out and see what may be the cause. So, my goal is to introduce simple to increasingly complex queries and show you the...

  • Viewing Performance Counters in SQL Server 2005

    I like Performance Monitor (perfmon); its a good tool for diagnosing many different types of problems. Its great for determining from how well your memory is handling requests to how well your CPU usage is doing...its also great for checking out different SQL Server metrics. In SQL Server 2005, the sys.dm_os_performance_counters Dynamic Management View (DMV) is introduced to allow you to query SQL Server related performance counters directly through the database engine. This makes is very useful...

  • Using APPLY to match a table valued function to a table

    In this article, I will explain how to use the APPLY operator to join the results of a table valued function to a table. The APPLY operator allows a table column to be supplied as an input parameter to a table valued function. Microsoft describes the APPLY operator as "The new APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression" ( source ). Basically what that means is that you can join a table valued function to...

  • Relating SQL Server profiler with Performance monitor

    A DBA would often experience the need to explore performance bottlenecks, to create benchmarking reports and to overview the SQL Server performance as part of its daily existence. Any of the above (including the daily existence) can easily become a daunting task if the "tools of the trade" are not known and mastered. Hence, in this short article we will concentrate our attention on a very rewarding aspect of the Windows Server and SQL Server performance monitoring tools...

    ...

  • Introduction to SQL Server Profiler

    How often do you find yourself with the need to know what statements are occurring on your SQL Server? Well, if you're like me the answer is "ALL THE TIME". Constantly Im asked about processes running on the server if things are slow. Even more importantly, I like to capture data from the system to use for trending purposes and to proactively diagnose performance problems before someone comes and tells me about it. Luckily, SQL Server comes with tools to capture such data, and to save...

  • When an outer join becomes an inner join

    Enclosed you will find how an improperly constructed OUTER JOIN query can turn into an INNER JOIN. An OUTER JOIN is described as a join where you want all data from one table along with any matching data from another table. In SQL Server the default behavior of using JOIN creates an INNER JOIN. But sometimes even if you use LEFT JOIN or LEFT OUTER JOIN, the query that you build may end up being an INNER JOIN. The first thing we will do is setup some test tables. Before you critique the tables, realize...
    Filed under: ,

  • The Numbers Table (Round 2)

    Originally I wrote about The Numbers Table over at SQL Server Central . Over there I presented the structure and a couple options on how to populate it. I won't dwell on all of that again. I'm just going to keep it simple. What is a numbers table? A numbers table is a table containing sequential integer values. The size of your table will vary depending upon how you may choose to use it. Most practical applications that I have encountered have never called for more than 100,000 records but...

  • Fast searching of large (n)varchar values

    In this Tip of the Week post, we will discuss how you can index a large data field to allow fast equality searching. What does this mean? It means that you won't be able to do indexed LIKE searches, but you will be able to do indexed = searches. Why is this necessary? This is necessary due to the size limitation of indexes in SQL Server. SQL Server does not allow you to create an index on any field, or combination of fields, whose length is greater than 900 bytes. For varchar fields, each character...
    Filed under: ,

  • Comparing a Sub-Query and a UDF

    There has always been a lot of confusion in terms of performance between correlated sub-queries vs user-defined functions. So, I've taken it upon myself to try to figure out which one is more effecient. The results I have found are by no means conclusive, but will hopefully give you an idea as to when to use which approach as well as a general methodology for how to compare queries for effeciency purposes. First, I'll start by creating a scalar user defined function that accepts the EmployeeID...

  • How to allow multiple NULL values in a UNIQUE column

    SQL Server can guarantee unique values in two ways, Primary keys and a unique indexes/constraints. The primary difference between the two is that fields bound to a unique index or constraint can be NULL while a primary key field can not. For the purpose of the unique indexes and constraints, NULL values are treated equal. That means that you cannot have two NULL values. Fortunately, there is a workaround for that will allow you to store multiple NULL values while ensuring that non-NULL values are...

  • Viewing SQL Server Jobs and History

    In SQL Server 2000, it was cumbersome to allow those who were not members of the sysadmin group the ability to view SQL Agent Jobs and job history through Enterprise Manager. SQL Server 2005 has made this a lot easier through the use of specialized database roles in the msdb database for viewing such information. In the scripts below, I get around these SQL 2000 restrictions with a couple of custom objects that allow you to view SQL Agent Job status and history. First, I need to create a function...

  • Server Role Members

    Below is a quick and easy way to query SQL Server 2005 system views to determine which users belong to which server roles on the instance. SELECT rolename = rolep.name, membername = memp.name FROM sys.server_role_members rm JOIN sys.server_principals rolep ON rm.role_principal_id = rolep.principal_id JOIN sys.server_principals memp ON rm.member_principal_id = memp.principal_id To determine which users are members of your database level roles on your instance, see this article .
1 2 Next >
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems