Owner: SQLProf.com URL:http://www.sqlprof.com/ Join Date: Thu, 03 Apr 2008 07:06:47 -0500 Rating:0 Site Description: SQLProf's primary purpose is to serve as a forum for the discussion of tips, tricks, design principles, best practices, and Microsoft SQL Server-related news items of interest for those developing and supporting software using Microsoft SQL Server. Site statistics:Click here
Functions to Work with NULL Values 2008-04-02 23:25:00 Today I was asked what was the replacement for the IIF command in MS Access and how to manage nulls in SQL Server. So I decided to write about nulls, not that I like to work with them...but sometimes we don't have a choice ;) SQL Server may generate unexpected results when performing calculations on columns that have NULL values. So, to resolve this problem you need to use the ISNULL, NULLIF, and COALESCE functions of SQL Server 2005 when your columns hold NULL values. ISNULL: The ISNULL function replaces NULL values with the specified replacement value. The following is the syntax of the ISNULL function: ISNULL ( check_expression , replacement_value ) The function takes the following arguments: Read more:Functions
, Values
How to Use Aggregate Functions with NULL Values 2008-04-02 19:49:21 Effect of NULL Values
on AggregateFunctions
NULL values may cause aggregate functions to produce unexpected or incorrect results because these functions ignore NULL values for calculations. For example, if the Weight column of a table has 504 records, of which 299 records have NULL values, the average of this column calculated by using the AVG function may return incorrect values. The following code example demonstrates this: USE AdventureWorks GO SELECT AVG(Weight) AS 'Average Weight' FROM Production.Product When we execute the query, we get an average weight of 74.069219. We get this result because when calculating the average, the AVG function considers only the 205 rows that contain non-NULL values. However, it is expected that the function considers all th
How to combine multiple rows of data on same line. 2008-03-31 13:41:37 I’m often ask how combine
information on same line in a query. Let me explain, lets say that you would like to list all products from the Production.product table in the AdventureWorks database and that you would like to list ALL vendors for each product from the Purchasing.Vendor table on the same line. Look at the following example: You can see in the 3rd column that all venders are list separated by a comma. In the AdventureWorks database the relation between the Product table and the Vendor table in done through a third table called ProductVendor. Look at the following diagram: Let’s start with this simple query that uses inner joins to link all three tables: SELECT Production.Product.Name AS [Product Name], &
Avoid using local variables in your Select statements 2008-03-29 13:36:04 People are always surprised when I tell that their queries will run slower when they use local variables in their queries. Let me explain: If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a guesstimate for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer will do a better selecting a query plan. For example, execute both of these queries. The first one uses a local variable and the second one a literal: use adventureworks --first one with a local varaiable declare @StartOrderDate datetime set @StartOrderDate = '20040731' select * from Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl WHERE hdr.SalesOrderID = dtl.SalesOrderId A Read more:Avoid
, Select
Preventing Microsoft Excel or any other application from connecting to SQL Server 2005 2008-03-28 22:32:38 Today, I was asked a question on how to prevent MicrosoftExcel
from connecting to SQL
Server. At first I wasn’t sure how to respond to this, but I finally found a way. It is a simple trick that uses the FOR LOGON option now available since the SP2 release of SQL Server
2005. USE master GO CREATE TRIGGER Prevent_Apps_logon ON ALL SERVER FOR LOGON AS BEGIN IF APP_NAME() LIKE '%excel%' -- or any other app name! ROLLBACK END Hope this helps, Eric Technorati : SQL Server 2005,DDL Triggers,FOR LOGON,Security
Auditing Logins in SQL Server 2005 2008-03-28 22:14:52 When I teach my SQL
administration classes, I'm often ask if SQL Server
provides a mechanism to log user logins. Before SQL Server 2005 SP2, this task was possible but not reliable. In SQL Server 2005 this task is easy to implement using DDL triggers (Data Definition Language). One reason that people ask me this question, is because they need to comply with Sarbanes-Oxley. I will be writing a series of articles on auditing for SOX in the next weeks. As a consultant, I am frequently invited to attend meetings with Sarbanes-Oxley auditors to discuss the security and integrity of corporate data. they want to know who has access to the data, how access is granted, and how is the company monitoring to prevent someone from sneaking in, logging on, and doing somethin
Understanding SET STATISTICS IO and SET STATISTICS TIME 2008-03-23 09:15:07 Today, I want to talk about two commands that are often overlooked. SET STATISTICS IO and SET STATISTICS TIME these two commands are very useful when comes time to tune a query. Tuning a query seems simple enough. In essence, we want our searches to run faster. When I teach my class on “Optimizing SQL Server” I realize that many people find it difficult to accomplish this task. While there are many reasons why query tuning is difficult, this article will concentrate on one aspect. And that is that query tuning takes place in an environment that is often changing from second to second, making it hard to really know what exactly is going on. When you run a query, different server resources are used. One of those resources is the CPU time. If you run the same que Read more:Understanding
Scripting Multiple Objects 2008-03-19 18:57:34 The other day I found a way to script multiple objects using the "Object Explorer Detail" tab in Management Studio. To be honest, I never thought that this tab was useful. All you have to do is select the type object you want to script in the object explorer and do a multiple selection in the Object Explorer Detail window. Look at the example below: Hope this helps, Eric Read more:Multiple
How to Tune a Database Using the Database Tuning Advisor (DTA) 2008-04-05 08:51:14 The Database
Engine Tuning
Advisor (DTA) is a new tool in SQL Server 2005 that enables you to tune databases and improve the performances of your queries. DTA examines how queries are processed in the databases you specify and then it recommends how you can improve performance by suggesting the creation of indexes and statistics. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000. You can use DTA to tune a database by using workload (trace files) that were previously created with the profiler or you can tune a Query directly in Management Studio. A workload is a set of Transact-SQL statements that execute against the database that you want to tune. The Database Engine Tuning Advisor use trace files, trace tables, or Transact-SQL scripts as workload input when t
Pivots with Dynamic Columns in SQL Server 2005/2008 2008-04-12 09:13:51 Pivots in SQL
Server 2005/2008 can convert row into column data. Pivots are frequently used in reports, and are reasonably easy to work with. However, many people have asked me how to make the column list dynamic. Normally, this list is fixed, but many times the new columns are determined by the data at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement: PIVOT allows you to turn data rows into columns. For example, if you have a query like this: USE AdventureWorks GO SELECT * FROM (SELECT CustomerID, DATEPART(m, OrderDate) OrderMonth, SubTotal Read more:Dynamic
, SQL Server
How to Attach a database without a transaction log file (.ldf) 2008-04-17 11:46:48 If you ever lose the drive that contains your log file, your database will become suspect and will stop working. Notice that you cannot expand the database. If you do, you will get an error message. You do not need to restore the backup in such a crash. If you do, you will lose everything since your last BACKUP. All you have to do is to detach the database and reattach it without the log. SQL Server 2005 does allow you to do this. You can reattach the database by following these simple steps: first, you need to detach the suspect database: The “detach database” dialog appears, click ok. You might get an error just ignore it and the database will be detached. You will have to refresh the database list to remo
Recommend Site To A Friend
