June 2007 Blog Posts
When selecting data from a table we can select rows which contain more than 8094 bytes.
The problem arises when trying to sort those rows.
in SQL Server 2000 this code:
CREATE TABLE t1 (id INT IDENTITY(1,1), c1 VARCHAR(8000))
CREATE TABLE t2 (id INT IDENTITY(1,1), c2 VARCHAR(8000))
INSERT INTO t1 (c1)
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000)
INSERT INTO t2 (c2)
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000)...
My follow up article on MARS has been published on on SQLTeam.com.
In this follow up I cover some misunderstandings in trasaction handling when using MARS
as well as how to troubleshoot MARS related problems.
More here.
In SQL Server 2005 importing XML files became very easy.
OPENROWSET now supports the BULK keyword which lets us import XML files with ease.
A little example:
CREATE TABLE XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'c:\TestXml.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
SELECT * FROM XmlImportTest
DROP TABLE XmlImportTest
SINGLE_BLOB is recommended...
This book authored by Kalen Delaney whose Inside SQL Server books are compulsory reading for every DBA
hits the nail on the head with this one too.
The book goes into hardcore details of how the storage engine itself works.
Let's review what is covered in each chapter:
Chapter 1: Installing and Upgrading to SQL Server 2005
An overview of things to consider when migrating/upgrading to SQL Server 2005
Chapter 2: SQL Server 2005 Architecture
Hardcore stuff begins. talks about components of the engine and it's memory management.
Chapter 3: SQL Server 2005 Configuration
Covers Configuration Manager and general System Configuration
Chapter 4: Databases and Database Files
Covers pretty much...
A coworker who works mainly in C# wanted to know if there's an IsNullOrEmpty function.
After a brief NO, i've given him this one.
So far it works great.
CREATE FUNCTION dbo.IsNullOrEmpty(@text NVARCHAR(4000))
RETURNS BIT
AS
BEGIN
IF ISNULL(@text, '') = ''
BEGIN
RETURN 1
END
RETURN 0
END
GO
DECLARE @text VARCHAR(100)
SELECT @text = 'gdrfash5'
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = ''
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = ' '
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = null
SELECT dbo.IsNullOrEmpty(@text)
Arity is the number of arguments that a method takes.
For example
void MyMethod(int i1, int i2)
has an arity of 2, since it takes 2 arguments.
In C# the arity is marked with `
"So why is this important?", you might ask.
Well it isn't. Until you deal with reflection. :)
A while ago I had to create some code using CodeDOM and i was getting a few errors that the ` in code isn't correct
when i tried to compile the code.
Here's an example of how arity is shown using reflection:
List<string> list = new List<string>();
Console.WriteLine(list.GetType().ToString());
// output: System.Collections.Generic.List`1[System.String]
Dictionary<int, string> dict = new Dictionary<int, string>();
Console.WriteLine(dict.GetType().ToString());
// output: System.Collections.Generic.Dictionary`2[System.Int32,System.String]
You can...
... at installation time was... are you ready? OWC11 Yes... OWC11. Come on, are you kidding me??? this is so 2003 :))
A few days ago i showed how to split string with XML.
Now it's time for concatenation with XML.
DECLARE @t TABLE (col VARCHAR(10))
INSERT into @t
select 'aaaa' UNION ALL
select 'bbbb' UNION ALL
select 'cccc' UNION ALL
select null UNION ALL
select 'dddd'
SELECT * FROM @t
SELECT
(
SELECT col + ', ' as [text()]
FROM @t
ORDER BY col DESC
FOR XML PATH('')
) AS MyCsvList
Both of these (split and concat) methods aren't really anything special. They're just a new way of solving old problem.
But whenever i need them i...