This JOIN method is equivalent to the TSQL INNER JOIN expression using equality.
This method returns all columns from both tables. Once again, column name collision is avoided by appending "_Second" to the columns affected.
Read more →
The DIFFERENCE Method has no equivalent in TSQL. It is also refered to as MINUS and is simply all the rows that are in the First table but not the Second.
Read more →
The Product Method is the equivalent of the CROSS JOIN expression in TSQL. Because a DataTable does not allow anonymous or duplicate column names, we must rename the Second tables columns if they already exist in the First table.
Read more →
The implementation of the UNION operator is equivalent to the TSQL expresion UNION ALL WARNING*This means that duplicates can appear The First table is used to construct the new DataTables columns, so in effect, the columns in the Second table only need to have the same Data Types and not both Name and Data Type.
Read more →
All the methods that follow are static and the class name will be called SQLOps (SQL Operations). This is important when we start to implement the non-primitive relational operators and overloaded methods
Read more →
First off please excuse the title. A DataTable is to a relation as a dog is to a cat.
For those not .NET aware, a DataTable is the fundamental result set returned from the ADO.
Read more →
Here is an extension to the CSV to Row function that breaks the 8000 byte varchar limit.It is only limited to the size of your "Numbers" table (I think)
CREATE FUNCTION RowParser ( @Text TEXT, @Separator VARCHAR(3) = ',' ) RETURNS TABLE AS RETURN ( SELECT n, SUBSTRING(@Text collate database_default, n, CASE SIGN(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n) WHEN -1 THEN CASE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n))) WHEN 0 THEN DATALENGTH(@Text)-n+1 ELSE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n))) -1 END ELSE ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n) END) AS Data , DATALENGTH(@Text)/n AS Position , CASE SIGN(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n) WHEN -1 THEN CASE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n))) WHEN 0 THEN DATALENGTH(@Text)-n+1 ELSE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n))) -1 END ELSE ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n) END AS NumberStep FROM Numbers WHERE n BETWEEN 0 AND (DATALENGTH(@Text) - DATALENGTH(@Separator)) AND ((SUBSTRING(@Text collate database_default,n-DATALENGTH(@Separator),DATALENGTH(@Separator)) = @Separator AND n > 1) OR n = 1) ) GOI originally had an example using 10550 byte text string but it takes up to much real estate.
Read more →
SQL Server can only implement a One-To-Zero Or One relationship. When we attempt a One-To-One declaritevly, it turns out to be useless.Strange RI
Unfortunately, most real world cases need a true One-To-One Constraint.
Read more →
I am been mucking around with declarative constraints lately and have come across a few weird ones..
This first schema is the only possible way to implement a true one-to-one relationship in SQL Server declaratively It is such a pity it is impossible to actual add any rows, without issuing a NOCHECK CONSTRAINT.
Read more →
What is wrong with this Schema?
CREATE TABLE IdentityTable (ParentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY) GO CREATE TABLE ChildOfIdentity(ChildID INT NOT NULL PRIMARY KEY, ParentID INT NOT NULL CONSTRAINT FK_ForeignKey FOREIGN KEY (ParentID) REFERENCES IdentityTable(ParentID) ON UPDATE CASCADE) GO
Read more →
SQL Server can only represent 3 types of "relationship" constraints (FK) declaratively
One to One - (One to (0 or 1)) One to Many - (One to (0 or n)) Many to Many
Read more →
For some unknown reason I seem to be getting a lot of SQL questions regarding maths functions.
An earlier post was concerned with Prime and Perfect numbers (Post) but this time they have asked for Combinations and Permutations Just quickly the formulaes for them are: Combinations = N!
Read more →
I got an usual request today in reference to writing SQL statements to find Prime & Perfect Numbers.
After a bit of clarification, (Primes & Perfect numbers less than 1 million) I convinced them not to write an alogrithm in TSQL and that ideally you would have a table of Prime & Perfect numbers and just JOIN to those tables when needed.
Read more →
Calling non-deterministic functions is not allowed inside a UDF, which seems to preclude any sort of “random” number generation. Fortunately a UDF can be “tricked” into thinking it is deterministic by using views.
Read more →
The project I am on sources data from both Mainframe (DB2)and SQL Server. The data is delivered to the front end via web service methods as an XML stream.
In most situations all the data "joining" is already done in the middle tier, but for a particular use case, the front end must manage 2 different XML streams (one from DB2 and the other from SQL) First thought was to use the XML classes in .
Read more →
At the start of this year I took 2 months off to enjoy my family and farm.
During a particularly rainy period (precious precious rain) I decided to sharpen up my SQL skills by building games into SQL.
Read more →
The shop I am working at has just rolled out Smart Cards.
Each PC has a Smart Card reader connected via a USB port.
These Cards allow building and network access.
Read more →
Hello all and big thankyou to Graz et al for this handy site.
Although probably a bit off topic for a SQL log, but I'll be focusing on .NET as I am in the middle (read Deep End) of a large implementation of it.
Read more →