Where are Programmability Objects Stored in SQL Server?

SQL Server programmability objects such as stored procedures, functions, assemblies, etc. are widely used, especially in cases of a major database design where you want to have a well-structured database with code reuse and performance.   The need to know where are Programmability Objects Stored in SQL Server Even though many of us use these … Read more…

T-SQL Tip: Inserting Leading Characters to a String

This post, is actually a T-SQL tip, on how to easily insert leading characters to a string. The Scenario for Inserting Leading Characters Consider that  you have a fixed-length table column for which you want to add leading characters, so that all its records, values with the same number of characters. How to Insert Leading … Read more…

CDNUG Event Review – May 22, 2013 – Windows / SQL Azure

Earlier this month, on the 22nd, another Cyprus .NET User Group event took place. The event featured two hourly topics dedicated to the Microsoft Cloud and its services. The first topic was on Windows Azure for Developers and the second on Windows Azure SQL Database, formerly SQL Azure. The event was also live streamed and … Read more…

Internal Query Processor Error: The query processor could not produce a query plan

OK folks, it did happen at some point, to get this error message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services. I was trying to execute the following query: SELECT *  FROM dbo.tbl1 WHERE  tbl2ID=(SELECT id FROM dbo.tbl2 WHERE Code=’Code1′) AND  tbl3ID=(SELECT id … Read more…

Argument data type ntext is invalid for argument 1 of left function

When you try to run a string function directly on a text or ntext column, in this example the “LEFT” function, you will get the following error: Argument data type ntext is invalid for argument 1 of left function. Similarly, if you try to run the REPLACE function against a text or ntext column, you will … Read more…

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“, you can easily do that … Read more…

Retrieving Only the Date from a Datetime Value in SQL Server

This article discusses about 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 … Read more…

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 returns a unique value … Read more…

Updating SQL Server Tables Without Causing Blocking

Even though the SQL Server Database Engine automatically sets the best possible locking hints on the underlying database objects of the various T-SQL operations that executes, there are cases where we need to manually control locking as a part of the business logic in our T-SQL script.   About the NOLOCK Table Hint A popular locking … Read more…

Executing Heavy Set-Based Operations Against VLDBs in SQL Server

In this article, we will discuss about executing heavy set-based operations against Very Large Datatabases (VLDBs) in SQL Server.   Introduction 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 instance. I won’t enter the debate of Row-Based vs. … Read more…

Using Unicode in SQL Server

In this article, we will be discussing about using Unicode in SQL Server.   Introduction 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.   Example … Read more…