The object name ‘x’ contains more than the maximum number of prefixes. The maximum is 2.

This error usually comes up when you are trying to perform a SELECT..INTO across a linked server. The problem is that a 4 part name cannot be used as the “INTO” parameter. This will fail: [cc lang=”sql”] SELECT * INTO [remoteserver].RemoteDB.dbo.sysdtspackages FROM MSDB.dbo.sysdtspackages [/cc] The trick is to create the table on the remote server, then perform an INSERT INTO. [cc lang=”sql”] INSERT INTO [remoteserver].RemoteDB.dbo.sysdtspackages SELECT * FROM MSDB.dbo.sysdtspackages [/cc] Continue reading ...

SQL Server Join Algorithms

If you read execution plans enough, you’ve probably realized that when SQL Server joins tables together, it uses different internal algorithms. The three algorithms are: Loop Join Merge Join Hash Join These alogorithms that are used are based upon factors of the underlying data. Merge Join For the most part, this is the most efficient method of joining tables. As the name implies, both tables are essentially merged together, much like a zipper being zipped. It typically occurs when both tables that are being joined, are joined on keys that are presorted and contain all the same keys in both tables (for example joining a primary key with a foreign key). When one table contains keys that the other table does not have, the chance of the merge join being used is less likely. The physical profile of a merge join is very little CPU usage, and very little reads compared to other types of joins. Loop Join The loop join is more CPU intensive than a merge join. This join typically occurs when one worktable is quite a bit smaller than the other. As the word loop implies, the smaller table being joined is looped until it finds the matching key in the outer (larger) table. This join is most efficient when the resulting output is smaller than 5000 rows. When larger than that, the CPU and reads make the join less efficient. Hash Join A hash join is the least efficient of all joins, however that does not […]

SQL Server Denali – New Features

I’m hoping this is a collaborative post, because I do not know a lot about the capabilities of Denali, and the documentation looks pretty sparse. What I do know however is that everything I have seen looks promising.  The SQL Server development team is top notch in my opinion.  The direction they are travelling is very promising for us as SQL Server professionals. Onto the post.  So I downloaded the CTP version.  Which can be downloaded here Installation One note on the install.  You cannot install this on Windows XP or on Server 2003.  This is a little surprising because I can only imagine how many clients will not be able to upgrade.  The other surprising thing is that because many companies have not adopted Windows 7 yet on their desktops (and will likely never move to Vista), the developers will not be able to install SSMS (SQL Server Management Studio).  Because of this limitation, many people may find themselves calling this version SQL Server 2013.  As for the actual installation, it’s getting more cumbersome, and complicated, however the new features and added security are worth it. SQL Server Management Studio The first cool part is SSMS.  SSMS now boasts a “Powered by Visual Studio” text on the splash screen.  Digging into it deeper, you can see the changes.  The borders of the interface are a different color and the tabs that appear when you create a new query look different.  The color coding is nicer looking (table names are […] Continue reading ...

SQL Server Denali – Pagination using ORDER BY

SQL Server Denali (2012) now has additional features built into the ORDER BY Clause. This features makes pagination simpler and more efficient. Here is an example: [cc lang=”sql”] SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET 55 ROWS FETCH NEXT 30 ROWS ONLY; [/cc] Offset determines the start row to begin on, while the fetch next defines how many rows should be returned. Both parameters above can be variable driven. An example of this would be the following: [cc lang=”sql”] CREATE PROCEDURE dbo.spGetEmployees ( @StartRow int = 1, @RowCount int = 10 ) AS BEGIN SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET @StartRow ROWS FETCH NEXT @RowCount ROWS ONLY; END [/cc] As you can see, this new syntax makes pagination much simpler than having to roll your own in previous versions.

SQL Server Denali – EXECUTE Statement WITH RESULT SETS

When I had originally written this article, I was very excited that this would be an answer to a long-standing wish that I have hope SQL Server would support. I wished I could call a stored procedure without knowing what was in the result set and dump it to a table (or temp table). Currently in order to trap the result set from executing a procedure we need to perform work arounds – all because we do not dynamically know the columns and data types the procedure is returning. If there was a way SQL could facilitate this without a work-around, it would take SQL to a new level of abstraction and modularity. The WITH RESULT SETS feature does allow us to change the column names and datatypes of the result set a stored procedure returns, however it does not allow us to define a subset of columns to return. Therefore, if a new column is added to the result set of a procedure that is called using WITH RESULT SETS, an error will occur. The SQL Server Denali CTP can be downloaded here. Let’s take a look at this new feature, first we’ll create a sample stored procedure: [cc lang=”sql”] CREATE PROCEDURE spSalesOverAmt ( @Amount money ) AS BEGIN SELECT SalesOrderID, OrderQty, UnitPrice FROM Sales.SalesOrderDetail WHERE UnitPrice > @Amount END [/cc] Now let’s change the column names and the data types: [cc lang=”sql”] — Execute the procedure with a parameter EXEC spSalesOverAmt @Amount = 1200 WITH RESULT SETS ( […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!