Performance Benefits of using Expression over User Defined Functions

Summary No matter how simple a UDF is, there’s a large performance penalty paid when they’re used.  This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more.  The penalty is incurred because the SQL Server database engine must create its…

4

BCP command calling Stored procedure with dynamic SQL and not having the first statement as a Select fails with the following Error "Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column"

EnvironmentSQL Server 2005  AND SQL Server 2008. The same command works fine in SQL SERVER 2000.Steps to Reprocreate proc p1 asdeclare @typ sysnameselect @typ = name from (select ‘ntext’ as name) as t    <== FMTONLY mode does not execute this statement (used to work in SQL2000)declare @sql varchar(1000)set @sql = ‘select system_type_id, user_type_id from sys.types…

5

Exception when selecting from SYSINDEXES in SQL Server 2005

During our course of troubleshooting queries taking long time for compilation, co-incidentally we found that SELECT * FROM SYSINDEXES was giving us an exception. We were getting the following error in Management Studio whenever querying SYSINDEXES Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command.  The results, if…

2

BCP Command using code page 65001 fails, if both SQL Server 2000 and SQL Server 2005 tools are installed

Problem description: The BCP command with code page 65001 fails when both SQL Server 2000 and SQL Server 2005 tools are present on the server. Cause: This issues occurs because when both SQL Server 2000 and SQL Server 2005 tools are present on the server then the BCP.EXE is used from C:\Program Files (x86)\Microsoft SQL…

2

Unable to debug Stored Procedure on a Server from the Client Machine

When you try to debug a stored procedure on the server from the client machine, the debugger just runs through the code and does not stop at any executable statement or break-point. The debugger however works locally both at the client and server. No errors are logged in any of the machines in SQL Server…

1

Data type precedence while using UNION statement differs in behavior between SQL Server 2000 and SQL Server 2005

It originally seemed to be a quite complex problem, involving an update trigger that was failing in SQL Server 2005, but had never failed in SQL Server 2000, giving a message like “Syntax error converting the varchar value ‘whatever’ to a column of data type INT ” when the calling code was doing something simple…

1