How to Attach a database without a transaction log file (.ldf)(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

If you ever lose the drive that contains your log file, your database will become suspect and will stop working. 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... read more...

add a comment |category: Database |Views: 11

tags: Database another

Pivots with Dynamic Columns in SQL Server 2005/2008(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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. read more...

add a comment |category: Database |Views: 71

tags: Database another

How to Tune a Database Using the Database Tuning Advisor (DTA)(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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. read more...

add a comment |category: Database |Views: 4

tags: Database another

Functions to Work with NULL Values(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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 ;)... read more...

add a comment |category: Database |Views: 4

tags: Database another

How to Use Aggregate Functions with NULL Values(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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... read more...

add a comment |category: Database |Views: 18

tags: Database another

How to combine multiple rows of data on same line.(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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. read more...

add a comment |category: Database |Views: 57

tags: Database another

Understanding SET STATISTICS IO and SET STATISTICS TIME(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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... read more...

add a comment |category: Database |Views: 72

tags: Database another

Scripting Multiple Objects in Management Studion(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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. read more...

add a comment |category: Database |Views: 0

tags: Database another

Preventing Microsoft Excel or any other application from connecting to(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

Today, I was asked a question on how to prevent Microsoft Excel 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. read more...

add a comment |category: Database |Views: 4

tags: Database another

Avoid using local variables in your Select statements(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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. read more...

add a comment |category: Database |Views: 9

tags: Database another

Auditing Logins in SQL Server 2005(sqlprof.com)

submitted by ericvaillancourt(90) 4 years, 1 month ago

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. read more...

add a comment |category: Database |Views: 8

tags: Database another