I sat through an absolutely fantastic presentation on query optimization a couple of weeks back. The presenter did a fine job of covering many of the basics like SET NOCOUNT ON, minimize cursor use, avoid explicit or implicit functions in the WHERE clause and why not to use the sp_ prefix when naming custom stored [...]
There are a few primary reasons why Windows Authentication should be considered a best practice. Since passwords aren’t visibly exposed in configuration files and credentials are not sent over the network, your systems tend to be more secure. Additionally, password management (expiration periods, minimum lengths and account lockout after multiple invalid logon requests) becomes a heck [...]
Functions like “getdate()” and convert(varchar(10), MyDateColumn,101) commonly show up in the WHERE clause of queries. Here’s an example: select c.LastName from Customer c with (nolock) where c.LastUpdated < getdate() You might think “no big deal.” It’s just a function, but for sql server, it almost always turns a deterministic query into a non-deterministic one. The [...]
Continue reading about DBTalk – Avoid Functions in the WHERE Clause
Over the past 6 months, I’ve subscribed to the “DBTalk” distribution list at work and I’ve really been enjoying it. Here’s are my top 2 reasons: 1. Though I know there are many .NET coders who also happen to be very capable database designers and developers, I believe that the many of us still have a lot to [...]
This question circulates through the office about once a year. “Um, I have Windows service which processes requests which are queued in a database table. Everything is working great, but I would like to add a second instance of the service and I need to know the best way to ensure the services don’t step [...]
Continue reading about What’s the Best Way to Manage a Database Queue?
I picked up a new development box the other day and I spent most of yesterday (heck, it was ALL of yesterday) getting the machine configured. Nearly all software installed without any issues except for SQL Server 2005 Developer Edition. After completing the installation, I planned to validate the installation by first checking to see [...]
We are currently going through the exercise of converting our web applications (primarily .NET web applications) from SQL Server Authentication over to Integrated Authentication. Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, [...]
Along with the SQL Server, Database, Username and Password, don’t forget to include the Application Name in your connection string. It’s an optional parameter, but it can be a lifesaver. Data Source=myServer; Initial Catalog=myDB; User Id=myUsername; Password=myPassword; Application Name=myApp; Consider this example: There are multiple .NET applications running on a single web server. Each application [...]