Consider the following scenario: You created a Window or SQL login in SQL Server and in the user mapping along with assigning a database role membership, you assigned as default schema something different than the “dbo”. Take as an example the following screenshot: As you can see, the SQL login…
How to retrieve size information for all the tables in a SQL Server Database
In an older post, I described how you could rebuild all the indexes of a database in SQL Server by making use of the undocumented stored procedure “sp_MSforeachtable”. Another common task is when you want to retrieve size information for all the tables in a database. Again, by using “sp_MSforeachtable“,…
Useful T-SQL Knowledge
A few days ago I started writing an article that it would provide a set of useful tips on SQL Server topics that are met in the everyday life of DBA/Database Developer. The article is now completed and you can find it below! It is organized in the following categories: Solutions…
Retrieving Only the Date from a Datetime Value in SQL Server
There are many times where you might need to only retrieve the date from a datetime value. For example, you may have today’s datetime (2011-10-10 20:30:28.230) and you want to only get the following value: 2011-10-10. In SQL Server 2008 or later, you can easily do this by casting/converting the…
Sequence Objects in SQL Server
SQL Server “Denali” among other introduces Sequence Objects. I am sure that sequence objects are widely known but let’s provide their definition in plain words: A sequence object allows you to set a global counter of values within the scope of a DBMS instance.Whenever you call the sequence object it…
Using Proxy Accounts in SQL Server Agent Jobs
When using SQL Server, in many cases you will might need to set up a SQL Server Agent job that will be accessing a resource within the domain. For example, you want to include a step in a SQL Server Agent job that based on some logic, will be handling a…
Backing up a Database in a Network Folder
Hi! I hope you are all doing well and having a great summer time! It’s been a while since the last time I have posted an article but hey, besides the heavy workload, it’s summer time and the beaches in Cyprus are really great! 🙂 This post discusses about a…
Executing Heavy Set-Based Operations Against VLDBs in SQL Server
Recently I had to design and execute some heavy set-based T-SQL operations against a large number of very large databases (VLDBs) within a SQL Server 2005 instance. I won’t enter the debate of Row-Based vs. Set-Based processing right now, as I plan to write an article on this in the…
Using Unicode in SQL Server
Unicode is the standard used in the computing industry for encoding and representing any text in the most written languages (…). SQL Server supports Unicode, thus allowing the easy storage and manipulation of data in the most languages. As I wanted to test this functionality I performed a simple experiment…