-
One of the challenges facing many development organizations is how to provide adequate development needs while providing enough isolation to prevent developer conflicts. Today's virtual technology provides the ability to spin up developer specific environments quickly easily while providing the horsepower...
-
In this article I will show you some options to recover from the error "Cannot open user default database. Login Failed". How does this happen? This happens when a login has a default database set, and that database is deleted. When you open any of the Microsoft database tools (like SQL Server...
-
In this article, I will show you the differences in using the row_number() function versus a correlated sub-select for filtering data. One thing that I have seen frequently is the use of the row_number() function to limit result sets. While row_number() has it's uses, using it to filter results is...
-
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...
-
I've been using the SQL Server product for quite some time now, and I almost always use the IDE programs that ship with the database engine. Enterprise Manager and Query Analyzer certainly had their quicks, but were overall nice tools. Management Studio 2005 was a great improvement over these tools...
-
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...
-
Sometimes you may not be able to see the characters in your data in SQL Server Mangement Studio (or another application). This simple function will allow you to pass an input string in, and it will return the individual characters as rows from a Table Valued Function. First the code: if object_id('...
-
If you have ever tryed to hash data in SQL Server using SQL Server's build in HASHBYTES , you know that that it doesn't accept greater than 8000 bytes of data. While that may work for a lot of scenarios, it doesn't help when you have large strings. Below we will go over the basics of a function...
-
Below is a quick and easy way to query SQL Server 2005 system views to determine which users belong to which database roles on your SQL Server databases. SELECT rolename = rolep.name, membername = memp.name FROM sys.database_role_members rm JOIN sys.database_principals rolep ON rm.role_principal_id ...
-
Table aliases are an often overlooked, underused, and confusing facet of database management systems. While table alaises may be confusing at first, they are wonderful for making queries easier to read, and easier to write. A table alias is nothing more than assigning a pseudo name to a table name in...
-
SQL Server Express is great for small business with small data requirements. It is essentially a scaled down version of SQL Server that is free. However, because it is throttled down there are some features that are missing...one such feature is the SQL Server Agent. This service is responsible for automating...
-
The SQL Server SELECT statement includes an INTO clause which is used to create a table object based on the resultset of the query. The table created by the INTO clause can be a session-level temporary table, a global temporary table, or a user defined table. The fields in the newly created table will...