By appending FOR XML auto to the end of your sql statement you can have your sql recordset returned as XML. A very useful utility.
SELECT * FROM [Table Name] FOR XML auto
Depending whether you pull from one or multiple tables, your output is structured XML based on the database schema. In situations we you are joining two tables there are two ways to have data returned in a clean way, (1) User a View or (2) User sub-queries.
Comments off
With the release of SQL Server 2005, a feature has been added that makes paging data much easier. This feature is the ROW_NUMBER function. This function assigns consecutive row numbers for data returned from a query. One requirement of this function is you need to specify a column for ordering. Take a look at the table and query below,
[ tbl_Chair ]
ChairID ChairName
——- ———-
2 Aeron
65 Designer
14 Staples
23 Ergonomic
39 Blue
select row_number() over (order by ChairID) as Row, ChairName from tbl_Chair
Row ChairName
—- ———-
1 Aeron
2 Staples
3 Ergonomic
4 Blue
5 Designer
Now if I only wanted the 3rd thrugh 5th row, I could say,
select row, name
from (
select row_number() over (order by ChairID) as Row, ChairName from tbl_Chair
)
where row >=3 and row <=5
Now add a temp table to store the number of Chairs in your table to count the number of pages for paging, you have a stored proc like,
CREATE PROCEDURE GetChairs
(
@PageNumber int,
@ProductsPerPage int
)
…
And you have nice paging functionality.
First time I ran across a query like the one below,
SELECT CustomerID, CompanyName FROM Customers WHERE CustomerID = N'CAMBR'
I asked myself, why is there an ‘N’ before ‘CAMBR’. Well, the ‘N’ actually designates unicode. If your query contains a field that can only be represented by 2 byte characters (i.e. Asian or accented characters), you need to use the N before the value or SQL Server will replace the characters with garbage (usually question marks). Below is another example, USE Northwind
SELECT CompanyName, ContactName, Phone, Fax FROM Customers WHERE CompanyName LIKE N'%snabbk'p' ORDER BY CompanyName ASC, ContactName ASC
For most cases, you should avoid using unicode. Unicode makes your database larger and slower. Also in a nvarchar field you only can store 4000 chars instead of 8000 in a “normal” varchar field. If you have in index on a nchar or nvarchar field it will take twice of the size therefore reducing the performance. I spend a good amount of time working with the Japanese character set and have run across some of these issues. If you do use Unicode, all Unicode is not the same. The Win32 API, Java, ODBC/OLEDB, etc all use UCS-2/UTF-16 as their representation of Unicode (UCS-2 encodes Unicode using 16 bits per character instead of the variable 8-24 bits used by UTF-8). But, Oracle, among others, use UTF-8, which is a multi-byte representation of Unicode. So, in UTF-8, typical ASCII codes are single byte, and double-byte is only used if a Unicode character is interjected.
Despite the hundreds of stored procedures I have written that contain heterogeneous queries I always forget to set ANSI_NULLS and ANSI_WARNINGS. If you see the following error,
Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_Warnings options to be set for the connection. This ensures consisten query semantics. Enable these options and then reisse your query.
Just insert the following commands before creating/updating your STORED PROCEDURE.
SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO CREATE PROCEDURE spStoredProdc AS ... go SET ANSI_NULLS OFF SET ANSI_WARNINGS OFF
In the case of CUSTOM FUNCTIONS you can do the same,
SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO CREATE FUNCTION dbo.functionName ... END
Comments off
Despite the hundreds of stored procedures I have written that contain heterogeneous queries I always forget to set ANSI_NULLS and ANSI_WARNINGS. If you see the following error, Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_Warnings options to be set for the connection. This ensures consisten query semantics. Enable these options and then reisse your query. Just insert the following commands before creating/updating your STORED PROCEDURE.
SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO CREATE PROCEDURE spStoredProdc AS ... go SET ANSI_NULLS OFF SET ANSI_WARNINGS OFF
In the case of CUSTOM FUNCTIONS you can do the same,
SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO CREATE FUNCTION dbo.functionName ... END