I just got off from taking exam 71-432: TS: Microsoft SQL Server 2008, Implementation and Maintenance - which will become 70-432 when it goes live and is the successor of 70-431 (for SQL 2005)and here are just a few thoughts More...
Need some recursive processing in TSQL (SQL Server 2005)?
Needed some TSQL code again to retrieve descendant and ancestor entries in a self-referencing table. I've done this a couple of times already and although I can write on top of my mind, sometimes you just want to make life easier and have a script you can just modify a bit to fit the new requirement. More...
Here's an interesting I came across in the recent Simple Talk Newsletter (by Red Gate Software).
It talks about issues with new SQL 2008 data types (date/time related) and it's effect on .NET 2.0 datetime data types when SP1 is installed or not. Something to be aware of and one of those times that you're glad you have a web rather than a desktop application. (you only have to install SP1 on the server(s))
SQL Server 2008 has introduced a few new data types, among others the
new date types, like date, time, datatime2 and datetimespan. Because
.Net 2 was released before SQL Server 2008 has introduced these data
types, there are no classes that map to these new types in .Net 2.
But this has changed with .Net 2 SP1, which introduces the DateTimeOffset structure.
Read full article from the following link :SQL Server 2008: new data types and .Net 2 with and without SP1
Just ran into this interesting article on MSDN Magazine about using LINQ to test stored procedures. It would be a bad idea to test stored procedures using other stored procedures too (eg. insert SP then verify presence of inserted value using select/load SP) so traditionally we do it using ADO.NET object (conn, commands) to build commands. But this LINQ approach seems very interesting. Easy of building commands, intellisense, compile time checking among others. That's another good use for LINQ which I'm really starting to like (hopefully even after trying it for n-tier projects)
The article also comes with a virtual lab for hands-on experience.
The need to test a program that accesses and manipulates a back-end SQL Server®
database is very common. In many such cases, the application interacts
with the back-end data through the use of SQL stored procedures.
In
this type of scenario, you can think of the stored procedures as
auxiliary methods of the system under test; and they must therefore be
tested just like any other module in the system.
Read full article here
One of the requirement for an application I'm currently
working on is for the end user of a web application/site to be able to create
objects in the database.
There are a number of objects that must be created or
manipulated but for the sake of simplicity let's take for example creating a table with one
column. The SQL statement would look like:
CREATE TABLE [MyTable]
( [MyColumn] INT NOT NULL )
To minimize SQL injection, I was hoping I could use the sp_executesql stored procedure to come up with a
parameterized query (in addition to other preventive measures like validating input). Something like:
DECLARE @SQLString NVARCHAR(MAX);
DECLARE @ParamDefinition NVARCHAR(256);
SET @SQLString =
N'CREATE
TABLE @TableName (@ColumnName INT NOT NULL)';
SET
@ParamDefinition =
N'@TableName VARCHAR(128),
@ColumnName VARCHAR(128)';
EXECUTE sp_executesql
@SQLString,
@ParamDefinition,
@TableName = 'MyTable',
@ColumnName = 'MyColumn';
When I executed the statement it returned an error
"Incorrect syntax near
'@TableName'". Though I haven't worked with sp_executesql with
Data Definition Language (DDL) statements I have been using it for Data
Manipulation Language (DML) statements such as conditional selects and others so
I'm quite sure there is nothing wrong with syntax near @TableName by itself.
What could be wrong?More...
Subqueries are essentially a query (or queries) inside another query. That is for example a SELECT statement inside another SELECT statement.
There is already enough information available in the internet and written materials about subqueries including their common uses, when and how to use them (see bottom of post) and I will not elaborate on that further. However, I would like to share my experience where I almost abandoned an elegant solution using a subquery in favor of another approach because I couldn't get it to work for some reason.
Note that the scripts below are merely to illustrate the point and in no way demonstrates best practices, especially considerations in execution performance and actually could be achieved without a subquery but again only to simplify the point. More...