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.
Continue reading ...
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 ( […]
Often times you have a need to export a table from SQL Server into another format. The most common of these is Excel, CSV (Comma Separated Value), Access, or even to another SQL Server. Luckily, there is one tool native to SQL Server that can export to all these formats, the SQL Server Import and Export wizard. To start this wizard, simply right-click on the database that contains the table you want to export within Management Studio, then select Tasks -> Export Data. What pops up next is the SQL Server Import/Export Wizard. Select next to get past the splash screen then choose the data source. This is going to be the same data source as the database you selected to export the table from. Once you select the ServerName and Database name, select next. Next you want to choose the destination. This is where multiple export options come in. You can choose any of the options that appear in the destination drop down box. Once you select the export file type and file location, you will then be prompted to select the tables to output, along with additional options. This will assist you in outputting many different export formats for your table.
Continue reading ...
In order to create a view, use the “Create View” statement using the following convention: [cc lang=”sql”] CREATE VIEW dbo.vStoreAccountNum AS SELECT AccountNumber FROM Sales.Customer WHERE CustomerType = ‘S’ [/cc] Views are essentially stored SELECT Statements. They do not offer any speed advantage over regular SELECT statements. And they are not faster than a SELECT statement executed from within a stored procedure. They are mostly good for hiding complicated logic, or for providing an additional layer of abstraction from your source tables as a security measure. Along with a good indexing strategy, views can be used to simplify highly normalized tables. The more joins within a view, and the more columns it returns back, the slower it is going to be. If you use views, try to make them only perform one function and only return back a finite number of columns. You don’t want views that return back a lot of columns when the majority of your queries only use one or two columns. Here are some Do’s and Don’ts: Do use views if you want Data Analysts to access common queries. Do make sure all the join columns in a view contain indexes. Do Not call views from within views. Try not to return a lot of columns within a view if the view is referenced a lot. Do Not use SELECT * within a view. Specify the columns names explicitly. Indexed (or Materialized Views) A materialized view is a view that has been indexed and has essentially […]
The SQL Replace function replaces a value within a string with another value. The final output is the original string with the replaced value. There are three parameters to the Replace function. The first is the original string. The next parameter is the string to replace. And the last parameter is the value to replace the string with. Here is an example: [cc lang=”sql”] SELECT REPLACE ( ‘original string’, — Original String ‘string’, — String to replace ‘replaced’ — What to replace with ) [/cc] The output from this is: “original replaced”. One thing you need to watch out for is that if any of the parameters in the replace statement are NULL, then the string that is returned will also be NULL. So if passing parameters dynamically and there is a chance of NULL, make sure to use the ISNULL function and turn it into an empty string. You can also sometimes get away with using REPLACE rather than concatenating strings. I’ve done this before when you have a templated piece of text that needs values injected into it. [cc lang=”sql”] DECLARE @ResponseMessage varchar(max); SET @ResponseMessage = ‘Dear [firstname], thank you for signing up for [subscription].’; SELECT @ResponseMessage = REPLACE ( @ResponseMessage, ‘[firstname]’, pc.FirstName ) FROM Person.Contact pc WHERE ContactID = 1; SELECT @ResponseMessage; [/cc] Though it may not seem like much of a big deal, when dealing with many lines of concatenated text, it comes out much cleaner.
Continue reading ...
