СОДЕРЖАНИЕ

1.	СОВЕТЫ
1.1.	Операции с большими объемами данных в SQL Server (начало)
2.	ССЫЛКИ НА СТАТЬИ
2.1.	Статьи на русском языке
2.2.	Англоязычные статьи
3.	ФОРУМ SQL.RU
3.1.	Самые популярные темы недели
3.2.	Вопросы остались без ответа

СОВЕТЫ

Операции с большими объемами данных в SQL Server (начало)

По материалам статьи Joe Chang Large Data Operations in SQL Server
Перевод Виталия Степаненко

В статьях серии Анализ производительности рассматривались формулы внутренних затрат ресурсов, использующиеся в оптимизаторе запросов SQL Server для определения плана выполнения и сами затраты ресурсов на выполнение запросов для конфигураций с данными в памяти. Предыдущие статьи ограничились анализом затрат ресурсов на выполнение запросов для случаев, когда все нужные данные находятся в памяти, так что производительность не ограничивалась конфигурацией диска. Эта статья дает представление о запросах, работающим с объемом данных, большим, чем доступная память, и запросах, выполняющих операции записи, где производительность ограничена конфигурацией диска.

Пример, представленный в этой статье, показывает, что формулы внутренних затрат ресурсов SQL Server для операций ввода-вывода основаны на времени доступа к диску. Величины затрат ресурсов на операции ввода-вывода были зафиксированы несколько лет назад. Они неточно отражают производительность современных дисков, как и действительные затраты ресурсов на операции в памяти. Кроме того, модель затрат ресурсов на выполнение запросов SQL Server не совсем аккуратно отражает затраты ресурсов на операции ввода-вывода для Insert, Update и Delete (IUD). Для операций с небольшим количеством строк разница между внутренней моделью затрат ресурсов SQL Server и действительными затратами на выполнение запросов незначительно влияет на эффективность плана выполнения. В операциях с большим количеством строк план по умолчанию может работать существенно медленнее, чем план выполнения для определенного количества операций ввода-вывода.

Обзор формул оценки затрат ресурсов для построения плана выполнения

Для начала, может оказаться полезным обзор формул оценки затрат ресурсов для построения плана выполнения SQL Server, рассмотренных в статьях серии 'Анализ производительности'. Формула оценки затрат ресурсов на операцию Index Seek показана ниже. Затраты ресурсов на ввод-вывод зависят от количества физической памяти.

Затраты ресурсов на ввод-вывод (I/O Cost) =
0.006328500 + 0.000740741 на каждую следующую страницу (до 1GB)
или 0.003203425 + 0.000740741 на каждую следующую страницу (больше 1GB)

Затраты ресурсов процессора (CPU Cost) = 0.000079600 + 0.000001100 на каждую следующую строку

Формула оценки затрат ресурсов для множества операций Bookmark Lookup показана ниже.

Затраты ресурсов на ввод-вывод (I/O Cost) =
сумма значений 0.006250000 для всех операций (до 1GB)
сумма значений 0.003124925 для всех операций (больше 1GB)

Затраты ресурсов процессора (CPU Cost) = 0.0000011 на строку

Множество операций Bookmark Lookup - это не точное количество строк, а часть этого количества, обычно >95% от большого количества строк. Для обеих операций Index Seek и Bookmark Lookup базовые затраты ресурсов на ввод-вывод зависят от конфигурации памяти компьютера. Если объем памяти меньше или равен 1 гигабайту, то базовые затраты ресурсов для Index Seek составляют 0.063285. Если объем памяти больше 1 гигабайта, то затраты ресурсов на ввод-вывод составляют 0.003203425. Все другие затраты ресурсов на ввод-вывод не зависят от системной памяти (если только не используется неявным образом поиск по индексу). Ниже показана формула оценки затрат ресурсов на операцию Table Scan, хотя она также может применяться для сканирования кластерных и некластерных индексов.

Затраты ресурсов на ввод-вывод (I/O Cost) = 0.0375785 + 0.000740741 на каждую следующую страницу

Затраты ресурсов процессора (CPU Cost) = 0.0000785 + 0.0000011 на каждую следующую строку

Для всех операций изменения содержимого таблицы - Insert, Update и Delete (IUD) - затраты ресурсов на ввод-вывод примерно соответствуют формулам, приведенным ниже. Затраты ресурсов процессора точно соответствуют формуле. Общие затраты ресурсов на операции Update и Delete включают затраты на поиск по индексу или на сканирование таблицы, но они могут быть не включены в отдельные значения затрат ресурсов на ввод-вывод и затрат ресурсов процессора.

Затраты ресурсов на ввод-вывод (I/O Cost) ~ 0.01002 - 0.01010 (>100 строк)

Затраты ресурсов процессора (CPU Cost) = 0.000001 на каждую строку

Интересный момент заключается в том, что затраты ресурсов на ввод-вывод для операций IUD фиксированные, несмотря на количество строк и страниц. Сравните это с затратами ресурсов на ввод-вывод для операций Index Seek, Bookmark Lookup и Table Scan, которые зависят от количества страниц.

Похоже, не существует никакой документации от Microsoft о единицах измерения затрат ресурсов плана выполнения SQL Server. Единица измерения может быть некой величиной времени или загрузки процессора. Статья 'Параллельные планы выполнения' показывает, что единица измерения является скорее категорией времени, чем загрузки процессора. В качестве основного доказательства этого утверждения можно привести то, что план выполнения с параллельными операциями требует меньше затрат ресурсов, чем эквивалентный план с непараллельными операциями. Логическим объяснением может быть то, что параллельные операции на двухпроцессорных компьютерах выполняются за половину времени плюс некоторые затраты времени на объединение результатов от каждого процессора. Если бы единицей измерения для плана выполнения выступала загруженность процессора, то параллельная операция показала бы более высокие затраты ресурсов. Это связано с тем, что разделение задачи между двумя процессорами не сократит общую сумму циклов процессора, и еще понадобятся дополнительные затраты ресурсов на объединение результатов.

Если предположить, что затраты ресурсов плана выполнения SQL Server измеряются единицами времени, то можно сделать следующее предположение, что единицей измерения является секунда. Значения затрат ресурсов не зависят от типа процессора и они не изменились при переходе с SQL Server 7.0 на 2000. Можно предположить, что затраты ресурсов были настроены на некой системе, когда SQL Server 7.0 был в разработке, где-то между 1995 и 1997 годами. Обоснованием этого периода является то, что среда, на которой были настроены единицы затрат ресурсов SQL Server, была базой данных с объемом данных, намного превышающим доступную системную память. В этом случае операции SQL часто сопроаождались бы операциями дисковыми ввода-вывода.

Для современного диска со скоростью 15K затраты ресурсов ввода-вывода для Index Seek в 0.0063285 секунды (или 6.33мс) являются достаточно произвольным временем доступа. Хотя этот результат и является чрезмерно низким для дисков со скоростью вращения 7200 RPM, доступных в середине 1990-х годов. Возможно, что был установлен коэффициент кэша буфера в 50% для затрат ресурсов на ввод-вывод для операций Index Seek и Bookmark Lookup для систем с памятью меньше 1 гигабайта и 75% для систем с памятью больше 1 гигабайта. Однако нет нужды ограничиваться только двумя возможными вариантами единиц измерения затрат ресурсов на ввод-вывод. Дополнительные затраты ресурсов на ввод-вывод в 0.0007407 на каждую страницу могут быть объяснены как время последовательного ввода-вывода (0.74мс), подразумевая, что скорость последовательной передачи данных диска равна 1350 операций ввода-вывода в секунду по 8KB каждая или 169 операций ввода-вывода в секунду по 64KB.

Вообще-то, память системы не может быть надежным показателем для коэффициента кэша буфера. Лучшей стратегией для настройки затрат ресурсов на ввод-вывод могло быть использование недавней истории коэффициента кэша буфера. Для операций с большим количеством строк также имеет смысл определить, сколько из необходимого количества данных уже находится в памяти.

Тестовая среда

Сервером тестовой среды является двухпроцессорный Xeon 2.4GHz/512K кэш, системная шина 533MHz и 2 гигабайта памяти на материнской плате с чипсетом ServerWorks GC-LE. Файл данных тестовой базы данных находится на двух 18-гигабайтных SCSI U160 дисках со скоростью вращения 15K, все log файлы находятся на одном SCSI диске со скоростью вращения 10K, а файл данных базы tempdb находится на диске IDE/ATA. Модель восстановления базы данных установлена в SIMPLE и 4 гигабайта дискового пространства заранее распределены для данных и лога. Кроме того, максимальный уровень параллельности был ограничен в большинстве тестов одним процессором. Результаты являются измерениями времени выполнения единственного запроса. Существуют некоторые отличия между измерениями, т.к. нет никакого простого способа определить содержание кэша буфера. Была сделана попытка произвести оценку с повторяющимися значениями.

Следующий скрипт создает тестовую таблицу и заполняет ее тестовыми данными. Таблица содержит 8 столбцов типа integer по 4 байта каждый, столбец типа char из 10 байт, столбец типа decimal из 5 байт, и 3 других столбца по 8 байт каждый, в сумме составляющие 71 байт. Вместе с метаданными все это требует примерно 81 байт на строку, позволяя 99 строкам поместиться в каждую 8-килобайтную страницу с коэффициентом заполнения 99%, оставляя свободными 77 байт.

CREATE TABLE M3C_00 (
ID int NOT NULL,
ID2 int NOT NULL,
ID3 int NOT NULL,
ID4 int NOT NULL,
ID5 int NOT NULL,
ID6 int NOT NULL,
SeqID int NOT NULL,
DistID int NOT NULL,
Value char(10) NOT NULL,
randDecimal decimal (9,4) NOT NULL,
randMoney money NOT NULL,
randDate datetime NOT NULL,
seqDate datetime NOT NULL )

Следующий скрипт заполняет таблицу 10 миллионами строк. Столбец ID - это последовательность от 1 до 10 миллионов, но он не объявлен явно как столбец типа identity. Столбцы с ID2 по ID6 не важны в этих сериях тестов, и в них могут быть использованы любые значения. Значения столбца SeqID последовательны, а значения столбца DistID разнесены по таблице. В этом примере таблица имеет 10 миллионов строк. Первые 100 000 строк имеют значение столбца SeqID, равное 1; следующие 100 000 имеют значение столбца SeqID, равное 2, и т.д. Первая строка имеет значение столбца DistID, равное 1; следующая строка со значением столбца DistID - строка 101, и т.д. Все строки с определенным SeqID являются смежными строками. Все строки с определенным значением DistID разделены 100 строками, что приводит к тому, что каждое отдельное значение DistID находится в отдельной 8-килобайтной странице. Таблица не кластерная, поэтому т.к. нет гарантии определенного размещения строк, то строки вставляются последовательно.

BEGIN TRANSACTION
DECLARE @I int, @rowCnt int, @p int, @sc1 int, @dv1 int
SELECT @I = 1, @rowCnt = 10000000, @p =100, @sc1 = 100000
SELECT @dv1 = @rowCnt/100000
WHILE @I <= @RowCnt BEGIN
INSERT M3C_00 (ID, ID2, ID3, ID4, ID5, ID6, SeqID, DistID,
Value, randDecimal, randMoney, randDate, seqDate)
VALUES ( @I, @I, @I/2, @I/4, @I/10, @I/20,
(@I-1)/@sc1 + 1, -- Последовательные значения
(@I-1)%(@dv1) + 1, -- Разнесенные значения
CHAR(65 + 26*rand())+CHAR(65 + 26*rand())+CHAR(65 + 26*rand())
+CONVERT(char(6),CONVERT(int,100000*(9.0*rand()+1.0)))+CHAR(65 + 26*rand()),
10000*rand(), 10000*rand(),
DATEADD(hour,100000*rand(),'1990-01-01'), DATEADD(hour,@I/5,'1990-01-01') )
SET @I = @I+1
END
COMMIT TRANSACTION
CHECKPOINT

Этот скрипт требует 17 минут 45 секунд для выполнения, вставляя в среднем 9380 строк в секунду. Таблица M3C_00 заканчивается на 105 264 странице со степенью заполнения 95%. Несмотря на генерацию таблицы каждый раз, когда нужен свежий набор данных, исходный набор данных сохранен в таблице M3C_00 и копия исходного набора создается при помощи команды SELECT INTO, показанной ниже.

SELECT ID,ID2,ID3,ID4,ID5,ID6,SeqID,DistID,Value,randDecimal,randMoney,randDate,seqDate
INTO M3C_01
FROM M3C_00
CHECKPOINT

Эта таблица имеет 101 012 страниц, примерно 99 строк на страницу со степенью заполнения 99%, и размера чуть больше 789 мегабайт. Команда SELECT INTO выполняется примерно 28 секунд, вставляя в среднем 357 000 строк в секунду. Следующие 2 индекса создаются на столбцах SeqID и DistID. Каждый индекс занимает 18 554 страницы или 145 мегабайт.

CREATE INDEX IX_M3C_01_Seq ON M3C_01 (SeqID) WITH SORT_IN_TEMPDB
CHECKPOINT
CREATE INDEX IX_M3C_01_Dist ON M3C_01 (DistID) WITH SORT_IN_TEMPDB
CHECKPOINT

Первый индекс требует примерно 30 секунд для создания с использованием памяти в 256 мегабайт, а второй требует 60 секунд.

(Продолжение следует)

[В начало]

ССЫЛКИ НА СТАТЬИ

Статьи на русском языке

Пять заблуждений связанных с .NET
Гайдар Магдануров
ASP: За несколько лет переписки с людьми, начинающими осваивать технологии .NET (читается "дот нэт") у меня накопился небольшой архив основных заблуждений связанных с новой платформой. Большая часть из них достаточно смешна и достойна пополнить собой репертуар эстрадных комиков, но, так или иначе, это настоящие причины останавливающие реальных людей от перехода на .NET. Поэтому, я полагаю, эту статью стоит прочитать тем, кто планирует вводить .NET в своем отделе или всей организации. Я постараюсь показать, как переубедить людей "одержимых" подобными заблуждениями...
Microsoft Visual Studio 2005. Часть 1. Некоторые новшества в IDE
Гайдар Магдануров
В данной статье рассматриваются новые функции и инструменты Visual Studio 2005, новые, либо изменившиеся по сравнению с предыдущими версиями Visual Studio 2002 и Visual Studio 2003. Статья не претендует на полноту описания всех новых функций, большинство из которых в Beta версии работают все еще недостаточно устойчиво. На настоящий момент Visual Studio 2005 (кодовое имя Widbey) официально существует в виде Beta версии, несмотря на то, что, по моим сведениям, в peer-to-peer сетях распространяются дистрибутивы несуществующих еще Release Candidate и Release версий. Поэтому все, что будет описано в статье относится только к Beta версии Visual Studio 2005 и может быть неверно в финальной версии. В статье использована информация из официальных источников корпорации Microsoft...

[В начало]

Англоязычные статьи

Lookup Table Madness
Don Peterson
Over the years I have had a number of "discussions" about the practice of creating generalized "lookup" tables for storing multiple code types in a single table.I won't keep you in suspense about my view of this practice; the idea stems from a failure to learn and appreciate the fundamentals of good database design. Accordingly, I developed an appropriate name for these tables: Massively Unified Code-Key tables, or MUCK tables
Change How You Access Data
Peter DeBetta
ADO.NET 2.0 lets you do data paging, make asynchronous database calls, leverage Multiple Active Result Sets, and more. This article is excerpted from Chapter 6, "Client-Side ADO.NET," of Peter DeBetta's upcoming book, Introducing Microsoft SQL Server 2005 for Developers [Microsoft Press, ISBN: 073561962X]. It has been edited for length and format to fit the magazine. You can read a PDF of the full chapter here
OSQL Utility
Don Schlichting
This article will explain and define the OSQL utilities ability to run scripts for administration and batch work when using Microsoft SQL Server
Automating "Generate SQL Script"
Muthusamy Anantha Kumar
MAK examines how to automate the "Generate SQL Script" by using a stored procedure to take advantage of "scptxfr.exe" provided by Microsoft
Working With Columns That Contain Null Values
Gregory A. Larsen
When a column in a row contains a NULL what does this mean? So what is a NULL? Is a NULL value a zero, space, or something else? From a SQL Server perspective a NULL is not a value, it only means that a value was not provided when the row was created. Special considerations need to be taken into account when dealing with nulls in SQL Server. This article will discuss some programming issues to be aware of when working with nulls, and some different techniques that can be used when dealing with nulls
Fighting OS-Level Fragmentation
Brian Moran
Q. I've read about how fragmentation at the SQL Server level can hurt my queries' performance. I understand how to look for fragmentation within my SQL Server tables by using the DBCC SHOWCONTIG command. However, should I also worry about fragmentation at the OS level?
Notification Services - II
Narasimha Rao AV
Notification services, one of the features provided in Yukon (SQL Server 2005), is a framework to send millions of notifications to millions of subscribers without hampering or compromising on performance. My previous article on Notification Services - I explains what notification services are and how they can be used in today’s world of automation. It also shows implementation of a sample (Bigshot sample) using Notification Services to get notifications when any interested event occurs in Bigshot’s life. I ended my earlier article with a note that it didn’t cover the implementation of custom event providers, custom formatters and custom delivery protocols. This article is a sequel to my first article, extending notification services implementation to cater the complex requirements of business applications
Introduction to SQL Server 2000 Reporting Services
Mike Gunderloy
Lots of people (myself included) are excited about the coming release of Microsoft SQL Server "Yukon," the successor to SQL Server 2000. But you don't have to wait until 2005 to see one of the advances that "Yukon" is delivering to developers. Last month Microsoft released SQL Server 2000 Reporting Services, an add-on for the current version of SQL Server (it will be packaged with new copies of SQL Server 2000 later this year). In this article, I'll introduce Reporting Services and show you how you might make use of it in your own development efforts
Frequently used DBCC Commands in SQL Server
Hari Prasad
This article details the various Database console commands available in SQL Server. DBCC commands are executed to verify and correct the physical and logical consistencies of a database. This article explains the usage and benefit of using some useful DBCC commands which can be used in day-to-day manner
Sending SQL Notifications with CDOSYS
Desiree Harris
While SQL Server has a very mature set of tools, one thing that is lacking is the ability to send a simple email alert based on a pre-defined set of rules. At first glace, it appears that email notifications exist but what becomes a problem to many people is the requirement for a MAPI subsystem to be present on the server. This means that Outlook or an equivalent program has to be installed on the server. Not only that, to properly configure it, the administrator will need to log into the server as the user that the SQL Server service is running under. I have always found this inconvenient so I set out to create a stable, free, easy to configure means of accomplishing the same thing without installing anything special on the server. I've included steps below to complete this task successfully
ADS Enables "Hands Off" SQL Server Installs
Rob Hawthorne
Does setting up SQL Server on multiple servers mean running setup.exe over and over? No, it doesn't. With the ADS Controller, you can deploy SQL Server to many servers at once, without having to visit each machine individually. Microsoft's free server tool, ADS (Automated Deployment Services), provides system administrators with a consistent way to deploy the Windows OS, but what about RDBMS deployment? Does setting up SQL Server on a number of servers mean you have to run setup.exe again and again, specifying the same parameters each time? No, it doesn't
Snapshot Isolation Adds Flexibility in SQL Server 2005
Ron Talmage
'Snapshot' is a new isolation level that reduces blocking reads and allows transactions to read previously committed versions of data, instead of uncommitted data. Picture your data access in a whole new light. SQL Server uses isolation levels to govern the tradeoff between consistency of transactions and concurrency of multiple users. As you raise a transaction's isolation level, you also increase the chances that a transaction's reads may block and deadlock with other transactions. Even with the relatively low default READ COMMITTED isolation level, transactions may experience some blocking and deadlocking due to reading data. SQL Server 2005 introduces a new type of isolation level called SNAPSHOT that is designed to allow transactions to read older but consistent versions of data without blocking other transactions. In this way, SNAPSHOT isolation can preserve the consistency of the READ COMMITTED isolation while reducing the frequency of blocking and deadlocking
BACKUP using NO_TRUNCATE and RESTORE
Tibor Karaszi
The BACKUP LOG command has an option named NO_TRUNCATE. This is used when you have an inaccessible database (suspect, for instance) and you want to backup the orphaned transaction log for that database
Changing the name of a SQL Server machine
Tibor Karaszi
If you change the machine name of a machine with SQL Server installed, you need to do a few things in SQL Server after the machine name change. This article outlines those steps. The aricle applies to SQL Server 7.0 and 2000
Why you want to be restrictive with shrink of database files
Tibor Karaszi
Introduced in SQL Server 7.0 was the ability to shrink the physical size of database data and transaction log files. This can be very useful under special circumstances, for example after archiving data to some other location. However, we often see DBA's doing this on a regular basis and the purpose of this article is to explain some of the downsides of shrink and what actually happens when you shrink a database file
Minimizing data loss when accidents happens
Tibor Karaszi
"Help! I just deleted all rows in the invoice table! Can you undo the operation?"
Recommended actions for corrupt or suspect databases
Tibor Karaszi
Encountering a suspect database or corruption in a database is a rare thing. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file)
Restore all databases from a number of backup files
Tibor Karaszi
Sometimes you find yourself with a number of backup files and you want to do restore from each of these backup files. Enterprise Manager can create backup history based on a backup file which can assist with restore from Enterprise Manager. This is done per backup files, though. And if you also need to specify a new path for the database files, you are in for going through a lot of dialogs
Generate DDL scripts
Tibor Karaszi
Here you find some techniques and products that can generate DDL scripts for your database objects and/or INSERT statements for your data
Stored procedure recompiles and SET options
Tibor Karaszi
Using stored procedures is generally considered a good thing. One advantage of stored procedures is that they are precompiled. This means that at execution time, SQL Server will fetch the precompiled procedure plan from cache memory (if exists) and execute it. This is generally faster than optimizing and compiling the code for each execution. However, under some circumstances, a procedure needs to be recompiled during execution
The ultimate guide to the datetime datatypes
Tibor Karaszi
The purpose of this article is to explain how the datetime datatypes work in SQL Server, including common pitfalls and general recommendations
Changing Domains for a SQL Server 2000 Failover Cluster
Microsoft Team
This step-by-step article describes the steps you must use to change domains for an existing SQL Server 2000 Failover Cluster
Performance Optimizations for XML Data Type in SQL Server Yukon
Microsoft
In this paper, you will learn several techniques to improve the performance of queries against and modifications to the XML data type in the latest version of Microsoft SQL Server™, SQL Server "Yukon." To get the most value from this paper, you should have some familiarity with XML features in the context of SQL Server. For an introduction to these features, search for the relevant topics in SQL Server "Yukon" Beta 1 Books Online
SQL Server Yukon Data Mining Tutorial
Microsoft
Microsoft® SQL Server™ "Yukon" Beta 1 provides an integrated environment for creating and working with data mining models. This tutorial uses four scenarios, targeted mailing, forecasting, market basket, and sequence clustering, to demonstrate how to use the mining model algorithms, mining model viewers, and data mining tools that are included in this release of SQL Server
XML Best Practices for SQL Server Yukon
Microsoft
This document provides guidelines for XML data modeling and usage in SQL Server? "Yukon." Some examples are provided to illustrate the ideas. To get the most from this paper, readers should have a basic understanding of XML features in the context of SQL Server. For an introduction to these features, search for the relevant topics in SQL Server "Yukon" Beta 1 Books Online
Getting Time Right in Analysis Services
Tom Chester
Rare is the OLAP cube that does not include a so-called Time dimension – a dimension that allows data to be analyzed over time. Though often named simply “Time”, the typical Time dimension is actually a date dimension, with a hierarchy consisting of Year-Quarter-Month-Day or the like
Authenticating a User Against a SQL Database
Ric Castagna
This how-to tip has evolved from some of my own personal challenges, as well as a few posts I’ve seen on developer forums such as SqlJunkies.com and DotNetJunkies.com. For me, this quest started about five years ago and has evolved over the years, but the root question has remained the same: "How can I authenticate a user without creating a Windows user account?" Since I do not host the sites I create, having the permissions to create users/groups is generally not available. Now that many hosting providers offer some measure of the available database, it is much easier to create a table to hold the username and password combinations for the authorized users
Notification Services in SQL Server 2005
Narasimha Rao Av
Notification Services is one of the features included in SQL Server 2005. As the name implies, Notification Services are services which send notifications to the interested entities based on what they would like be notified on. The addition of Notification Services to SQL Server 2005 has inherent advantages as most of the notifications are based on data changes (or additions). Combining Notification Services with SQL Server makes applications more scalable without hurting performance. To put it in simple terms, Notification Services are services that check if any event has occurred on the specified data, check if any entity has subscribed to be notified when that event occurs, and sends the notification to that entity. It looks simple, but the architecture of Notification Services makes it so flexible that you no longer have the pains of implementing features such as polling events, scheduling, formatting and delivery of notifications. These features are built in to Notification Services for easy integration and development
Yukon and the CLR
Srinivas Sampath
Yukon, the code name for the next generation of SQL Server, promises many improvements over the existing version of SQL Server, SQL Server 2000. Recently it was announced that Yukon's offfical name will be SQL Server 2005 and shipped by early 2005. There will more betas of the product this year as Microsoft works on the various feedback and enhancements cited by the early adopters. Among the many improvements, the one thing that really interests me is the integration of the .NET Framework within the SQL engine. What this means is that Yukon now hosts the Common Language Runtime (CLR), which will allow SQL Server developers to write code using .NET languages. The various pieces of code that you can now write include
Using SQL Server Yukon's .NET CLR Features Practically
Kent Tegels
When I was first introduced to Peanut Butter Chicken, I had many questions: What does it taste like? Why would one want to mix Peanut Butter and Chicken? How do you prepare it? Who thought this up? I was hooked for life with the first bite
Exploring SQL Server Yukon T-SQL Enhancements
Stephen Forte
By now you must have heard that you can write Yukon stored procedures in any CLR language like C# or VB .NET. This is great news if you have never mastered T-SQL, right? I hate to be the bearer of bad news, but CLR Stored Procedures are not a cure-all. Writing a stored procedure in a CLR language is good when you want to do some high-end processor intensive operations or call out to an external file or process. Where you might have used an extended stored procedure in the past, this place is now a candidate in Yukon for a CLR stored procedure. So, reports of T-SQL's death have been greatly exaggerated. You are still going to want to use T-SQL with most set-based operations. Actually, you will most likely find it more efficient (and performant) to use T-SQL in 95% of your Yukon queries and stored procedures
Yukon Demo: AdventureWorks Contacts
Jason Stowe
Starting with Yukon Beta 1, Microsoft SQL Server provides the ability to expose native HTTP methods inside the server. These requests and responses are made within SOAP packets, which are an extension of XML
SQL Web Bench v1.0
Microsoft Team
SQL WebBench is a stand alone browser based application that demonstrates Native SQL Web Services in Microsoft® SQL Server™ "Yukon" Beta 1. SQL Web Bench allows for HTTP SOAP endpoint manipulation and the ability to invoke remote SQL web services right from your browser via a WSDL XSL transform. SQL Web Bench demonstrates and exposes the raw SOAP that is sent and received from the server
Finding and Manipulating Trigger Statuses
Srinivas Sampath
In SQL Server 2000, you can attach triggers to database objects like tables to perform additional processing. For example, you can write a trigger to log entries into a database. Triggers can exist in two states, enabled and disabled. A trigger that is enabled will fire when the appropriate DML (data manipulation language) statement is used (for example, INSERT, UPDATE, or DELETE). A trigger that is disabled will not fire when the appropriate DML statement is used. Sometimes it is useful to know the status of the triggers on a table or the status of all the triggers in the database. Knowing this status can be useful to write many tools. For example, if your database uses triggers and you want to provide a facility for the application administrator to enable/disable the various triggers on a need basis, you can easily write code that presents all the triggers in the database along with their current status and the administrator can adjust the status after which you execute the necessary commands against the database to enable/disable the various triggers
SQL Server Performance - Common Symptoms and Tools (Part I)
Microsoft Team
A few common complaints from customers due to high CPU utilization are"SQL Server appears to be hung,” “SQL Server is not responding," "Queries are running slow," "Users cannot connect," "Web sites are timing out," and so on
SQL Server Performance - Common Symptoms and Tools (Part II)
Microsoft Team
Customers are normally good at identifying processor and memory bottlenecks, but they rarely start a support call with a statement like, “My disk subsystem is a bottleneck.” Therefore, identifying and resolving disk (I/O) subsystem bottlenecks are difficult cases. The cases usually start with, “The overall performance has degraded over time”, “It is just slow”, or “The workload has increased and everything is slow.” The customer can’t pinpoint one query or workload that is causing the slow performance
Queued Updating Subscriptions
Microsoft Team
Queued updating subscription is a new feature introduced in SQL Server 2000 transactional replication. It allows subscribers to modify the replicated data, and those changes will first be saved in a SQL queue by default. Later a SQL Queue Reader Agent, which runs at the distributor, will read those queued transactions and apply them to the publisher server
Identifying the Cause of Recompilation in an SP:Recompile Event
Microsoft Team
The SP:Recompile event in the SQL Profiler indicates when SQL Server recompiled a stored procedure. With SQL Server 2000 Service Pack 2 (SP2), the SP:Recompile event provides more information about why the recompilation occurs, which is helpful when you are troubleshooting recompile issues
SQLXML BulkLoad vs. bcp
Michael Cating
When I first learned of the BulkLoad object in the Microsoft SQLXML Web Release 1, I was ecstatic. Finally, Microsoft had provided a COM interface to wrap bulk-loading capabilities! Gone were the days of shelling out to bcp to get the performance of bulk inserts — or writing to a file on a network share and executing a BULK INSERT command. Sure, SQLXML requires installation of the SOAP Toolkit and the SQL Server Client tools, but if it eases my programming efforts (and I think most would agree that using a COM object is easier than shelling out to a command-line utility), it's worth it!
Viewing SQL Server 2000 Performance Data
Microsoft Team
This article describes views that you can use to analyze the performance data in the trace file that is created in the following article in the Microsoft Knowledge Base: 283696 INF: Job to Monitor SQL Server 2000 Performance and Activity
Tracing SQL Statements
Srinivas Sampath
One of the common questions that I see in the newsgroups is how to trace SQL statements executed by SQL Server. When we say tracing, what we mean is the set of operations that is executing on the server. Why is this useful? Many applications will execute a varied amount of SQL statements against a SQL Server and it is often useful to know what is happening at the server. For example, we might want to watch the time taken by each SQL statement or procedure that is being executed at the server. This is useful if we want to tune some bottlenecks of our application. The tool that allows you to watch what is happening at the server is called the SQL Profiler and it has many features! In this article, we will see a simple example that shows how to see the various SQL statements executing on the server
Moving Reporting Services' Virtual Directories
Trent Miesner
Like most 1.0 releases from Microsoft, Reporting Services provides a wealth of useful features that leave us wondering how we ever got along without them. Unfortunately, like other 1.0 releases, there are a few glitches that make life just a little difficult for early adopters
Check Boxes for Reporting Services Reports
Trent Miesner
Reporting Services offers a wonderful array of reporting features, but surprisingly leaves out one important feature you find in most other reporting tools: check boxes. Check boxes are vital for all sorts of reports such as surveys, checklists, etc. Fortunately, there’s a simple solution to this problem and I'll show you it in this how-to
Radio Buttons for Reporting Services Reports
In a previous article called Check Boxes for Reporting Services Reports, I showed you how to implement check boxes in Reporting Services. This article will detail a way to display radio buttons in your reports. Radio buttons are commonly used in surveys for things such as displaying a selected ranking from 1 to 10 or from Strongly Agree to Strongly Disagree
Using Query Analyzer on HPC 2000 and Pocket PC 2002 Devices in SQL
Microsoft Team
SQL Server 2000 Windows CE Edition (SQL Server CE) includes a graphical query analyzer named Isqlwce for Windows CE devices. The Isqlwce utility is installed in the Isqlwce folder of your SQL Server CE installation (by default, Program Files\Microsoft SQL Server CE)
Prebuilding a Database in SQL Server CE
Microsoft Team
If you have a large quantity of data in your back-end Microsoft SQL Server database, it can be resource-intensive to have every Microsoft Windows CE device download all of the data by using a replication synchronization process. You can "prebuild" a Microsoft SQL Server CE copy of the database, and download it onto each device with the application. This article provides the steps necessary to complete this task
Archiving More Than Six SQL Server Error Logs
Microsoft Team
This how-to article describes how to archive more than six SQL Server error logs. It includes a sample startup (or autoexec) stored procedure named sp_archivelog6 that can archive an infinite number of SQL Server error logs
Backing Up the Last Transaction Log When the Master and the Database Files Are Damaged
Microsoft Team
You can backup the currently active transaction log even if the database file is damaged, provided that the transaction log file is still accessible. On Microsoft SQL Server 7.0, both the primary data file and the transaction log files must be accessible to backup the last active transaction log. If the master database is also damaged, you can rebuild and restore the master database first, and then backup the last active transaction log of the inaccessible database. For example, both the data file and the master device might be on damaged media. However, if a master database backup is not available, you can backup the last active transaction log of the database by using the method that is discussed in this article, provided that both the primary data file and the transaction log files are accessible on SQL Server 7.0
Using the SQLIOStress Utility to Stress a Disk Subsystem
Microsoft Team
This article outlines the SQLIOStress utility. You can use this utility to perform stress tests on disk subsystems to simulate Microsoft SQL Server 2000 and Microsoft SQL Server 7.0 read, write, checkpoint, backup, sort, and read ahead activities
Testing a Full-Text Search IFilter in C#
Andrew Cencini
In this final article of my three-part series, we'll look at a code sample that may be used to test the output of a filter component (also referred to as IFilter after the interface being implemented). (See also Part I: Testing a Full-Text Search Wordbreaker Using C# and Part II: Testing a Full-Text Search Stemmer in C#.) This filter is used to extract text and property data from binary or formatted documents (e.g., Microsoft Word or HTML documents). In this article, I will not focus as much on the fairly extensive "baggage" that is involved in supporting the IFilter interface (there are many approaches to hooking the various pieces together), but rather I'll spend more time explaining how to get everything working so you can experiment further with your IFilters and fine-tune the techniques employed to make these components available in C#
Administering Different Versions of SQL Server by Using SQL Server
You cannot use SQL Server Enterprise Manager in SQL Server 7.0 to administer SQL Server 6.5 and earlier. Additionally, you cannot use SQL Server Enterprise Manager in SQL Server 6.5 or earlier to administer SQL Server 7.0 or later. You can only register computers running different versions of SQL Server by using SQL Server Enterprise Manager
Testing a Full-Text Search Stemmer in C#
Andrew Cencini
This article is Part II of a three-part series on code samples for testing various Full-Text components (Part I: Testing a Full-Text Search Wordbreaker Using C#). In this article, I'll start with a code sample that may be used to test the output of a stemmer — a language's stemmer generates inflected forms of a particular word based on various rules of that language. These inflected forms can include singular, plural, and possessive forms for nouns, and various tenses, numbers, etc. for verbs. A stemmer component is used for FREETEXT queries, as well as CONTAINS queries using the FORMSOF(INFLECTIONAL, …) syntax within a search expression
Increasing the Number of SQL Server Error Logs
Microsoft Team
By default, the SQL Server error logs are kept in the SQL Server's Log subfolder. By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6. The name of the current, most recent log is Errorlog with no extension. The log is re-created every time that you restart SQL Server. When the Errorlog file is re-created, the previous log is renamed to Errorlog.1, and the next previous log (Errorlog.1) is renamed to Errorlog.2, and so on. Errorlog.6 is deleted. This actually occurs in reverse order, but the result is the same
Testing a Full-Text Search Wordbreaker Using C#
Andrew Cencini
This article is Part I of a three-part series on code samples for testing various Full-Text components. We'll start with a code sample that may be used to test the output of a wordbreaker — the wordbreaker is one of the more fundamental pieces of Full-Text Search plumbing. A wordbreaker for a particular language tokenizes text (separates text into units, generally a word) based on word-boundary rules for that particular language. For example, variants of the English language primarily consider whitespace as a word boundary
Merge Modules and MSDE
Microsoft Team
Merge modules are a feature of Windows Installer that provide a standard method for delivering components, insuring that the correct version of a component is installed. A merge module contains a component, such as a .dll, along with any related files, resources, registry entries, and setup logic
Moving Tables and Indexes between Filegroups
Microsoft Team
In this bulletin, I will discuss two frequently asked questions that are centered around moving data between various database files. The instructions for accomplishing this task are available in SQL Server 2000 Books Online (BOL), but for various reasons these instructions may not be known to SQL Server support professionals and SQL Server customers alike
SQL Mail
Microsoft Team
SQL Server provides the ability to send and receive e-mail by using SQL Mail. SQL Mail has stored procedures and extended stored procedures that can be used to manipulate data, process queries received by e-mail, and return the results. This functionality of SQL Mail can be used to automate various processes
Introduction to Full-Text Search
Microsoft Team
The FTS feature allows you to retrieve specific text data from columns in a database. It does this by first indexing those columns, and then providing you the ability to query the indexes that were created. You may now be asking, Why can't I use the LIKE T-SQL predicate to do this? Well, the FTS feature gives you the following advantages over the LIKE predicate
SQL Server Connectivity
Microsoft Team
SQL Server 2000 supports several methods of communication between client applications and the server. When the application is on the same computer as an instance of SQL Server 2000, Windows Interprocess Communication (IPC) components, such as Local Named Pipes or Shared Memory, are used. When the application is on a separate client, a network IPC, such as TCP/IP or Named Pipes, is used to communicate with SQL Server
Changing the SQL Server 2000 Pull Subscription Working Directory
Microsoft Team
This step-by-step article describes how you can change the default working directory of a SQL Server 2000 pull subscription. The default working directory of a SQL Server 2000 pull subscription is the Windows Temporary folder
Connecting to a SQL Server 2000 Named Instance with JDBC
Microsoft SQL Server 2000 supports multiple instances of the SQL Server database engine that run concurrently on the same computer. The following are the two types of SQL Server database engine instances: default and named. There can only be one default instance that runs on any computer, and it is identified by the name of the computer on which the default instance runs. The computer name and instance name are typically specified in the following format
Implementing a SQL Server 2000 Desktop Engine Callback Function
Microsoft Team
This article describes how to implement a callback function during the installation of the SQL Server 2000 Desktop Engine (MSDE 2000) and also discusses all the conditions that must be met to make sure the callback function runs properly
Using Enterprise Manager to Remove the IDENTITY Property
Microsoft Team
To remove an IDENTITY property from an existing column of a table, follow these steps
Creating Apps with SQL Server CE and SQL RDA
Bill Wagner
The most underrated feature in .NET development is how you can leverage your skills in one area to another. You use the same tools and libraries for desktop applications, Web applications, Web services, and mobile applications. Yes, there are some differences between the .NET Framework proper and the Compact Framework, but you can very easily move from one to the other and leverage your design skills, language skills, and knowledge of the library and database engines
Accessing .NET Components from SQL Server
Srinivas Sampath
In the articles titled Using COM Objects in SQL Server and Extending SQL Server with COM Objects, we saw the basics of how to access and use COM objects from SQL Server. With the advent of .NET, it is natural for us to see if SQL Server can call into .NET objects. Unfortunately, you cannot, as described in the following article. But don't lose heart, you can access .NET objects in an indirect way using an intermediate COM wrapper. This COM wrapper can call into the .NET code using interop. In this example, we will see how to exploit this feature
Running Stored Procedures with ASP.NET
Wayne Freeze
If you’re writing SQL Server database applications (or Oracle or DB2 for that matter), you spend a lot of time building and testing stored procedures, especially those stored procedures that pass and return information through parameters rather than row sets. No matter how much you test these types of stored procedures using database tools, invariably when you plug in to your program, it doesn’t quite work the way you'd like
Creating and Saving Images to a Database in ASP.NET
While I was developing an e-commerce site, I was faced with the problem of populating the database with product images (large and thumbnails) for each product. Uploading two different sizes of the same image for each product seemed time-consuming and not practical, so I created a function that takes one image, creates a thumbnail of a specified size, and saves both images (the original and the thumbnail) to a SQL database. I then created a class to retrieve these images and display them in a Web page
Creating, Editing, and Executing Stored Procedures in Visual InterDev Microsoft Team
This article shows how to create, edit, and execute stored procedures in SQL Server by using Visual InterDev 6.0
Listing Database Properties Settings
At times, it may be useful to list the settings of all database properties for a given database, including some properties that the user cannot set, such as "suspect", "not recovered", and so on. This article has a stored procedure, sp_list_database_properties, that you can use to accomplish this. SQL Server 7.0 Transact-SQL provides the DATABASEPROPERTY(database, property) statement to check for specific database properties. You can use the stored procedure to list the settings of all currently available database properties for a database
Expanding Hierarchies with SQL Server
Microsoft Team
Databases frequently store hierarchical information. The SQL Books Online procedure declares @level, which is a reserved word in SQL Server 6.5 and SQL Server 7.0. Level is no longer a reserved word in SQL Server 2000. The following code is a corrected version of this procedure in which @lvl is substituted for the reserved word so that it works on all versions. The procedure is enhanced to include the CREATE TABLE statement, CREATE INDEX samples, and INSERT statements that are required to demonstrate the example
Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations
Microsoft Team
In some situations where stored procedures are recompiled, the cost of recompilation may outweigh the benefit that is derived from doing so. Note that because SQL Server Service Pack 2 (SP2) and earlier do not support statement level recompilation, the whole stored procedure must be recompiled when a recompile is triggered. Therefore, the number of recompiles that are triggered during the execution of a stored procedure and the length of the stored procedure may sometimes increase the overall duration of the stored procedure execution
Creating a Stored Procedure for Custom Paging with the ASP.NET DataGrid Control
Shannon Horn
The DataGrid control is the most flexible and robust data bound control offered by ASP.NET. The DataGrid control renders as an HTML table in the user’s Web browser that can contain any other combination of controls, can be dynamically manipulated using scripts and server-side code, accommodates in-row editing and updating of displayed data, sorting of columns, and built-in paging. The built-in paging supplied by the DataGrid control may either be semi-automatically managed by the control or be manually managed using code
Debugging an Extended Stored Procedure
This article describes how to properly debug an extended stored procedure
Using the sp_fixindex Stored Procedure
Microsoft Team
This article discusses how to use the undocumented sp_fixindex system stored procedure to correct problems in the indexes of SQL Server system tables
Mortgaging the Future
Kent Tegels
The other day I asked cyberspace what interesting yet practical mathematical problems they had written programs for. The answers varied widely, but one that reminded me of my own experiences was generate an amortization schedule. You would use such a schedule to show the progression of some significant loan where periodic interest is charged and payments are made. A common use of an amortization schedule is a mortgage — a loan commonly associate with the purchase of real estate like your house. The math for such a schedule itself is fairly easy and the programming involved is not overly complex
Date and Time Manipulation in SQL Server 2000
Manuj Bahl
SQL Server 2000 does not have separate data types for date and time. Instead the Microsoft SQL Server Team chose to combine both the data types into one and store it as a datetime data type. Date and time can be stored in SQL Server in datetime or smalldatetime. The datetime data type can store dates from January 1, 1753 to December 31, 9999 to an accuracy of up to 0.003 part of a second. The smalldatetime data type can store data from January 1, 1900 to June 6, 2079 with accuracy of up to the minute
Generating Calendar Dates
Anatoly Lubarsky
Many times developers need some data concerning calendar dates of the year in a usable format; for example, in a table, so you can fetch data, search data, create joins, and do other useful things needed in processing application business logic. The common problem is when you need to have the dates following one day after another in a table format
Building a Basic Flash MX Page with ASP.NET and SQL Server
Samuel Cromer
This how-to article describes how to create a basic Flash MX login interface that utilizes ASP.NET and SQL Server 2000
Passing a Variable to a Linked Server Query
Microsoft Team
When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to a linked server query
Returning Multiple Columns in a DTS Lookup Query
Microsoft Team
With the Data Transformation Services (DTS) Lookup object, you can retrieve data from locations other than the immediate source. A query and a connection is associated with the lookup. If the query returns more than a single column, the Lookup object returns the results in a zero-based array
Rotating a Table in SQL Server
Microsoft Team
This article describes how to rotate a SQL Server table. Suppose you have a table that is named QTRSALES. The table has the columns YEAR, QUARTER, and AMOUNT with the data in the following format (note that there is no row for the fourth quarter of 1996)
Using Transact-SQL to Iterate through a Result Set
Microsoft Team
This article describes various methods that you can use to simulate a cursor-like FETCH-NEXT logic in a stored procedure, trigger, or Transact-SQL batch. There are three methods you can use to iterate through a result set by using Transact-SQL statements
An XML Eye for the Database Guy (Part I)
Kent Tegels
A couple of weeks ago, my co-worker Phil and I were having a few beers when SQL Server "Yukon" and Extensible Markup Language (XML) came up. One of the most interesting things about Yukon is how deeply and extensively XML is supported. Phil also has been to a couple of my in-house training classes for XML. He had some feedback for me about them — some hard feedback. That got me thinking about how I presented on this topic. The material I was presenting was correct, but if you were not a developer, the classes were a bit like trying to learn English by reading and listening to the musings of Jack Kerouac. Reflecting on this, I decided that I needed to re-examine the way I present material. Or, as Jack himself might have said
Data Access Support in Visual Studio.NET (Whidbey)
Mark Fussell
The Microsoft Developer Tools Roadmap 2003-2005 describes the innovations and enhancements within the "Whidbey" release of Visual Studio and the .NET Framework
Retrieving XML from SQL Server Using SELECT ... FOR XML
James Culshaw BSc(Hons)
One of the best features in SQL Server 2000 is the ability to retrieve data in the form of XML via the use of the FOR XML clause appended to the end of a SELECT statement. The FOR XML clause has three modes — RAW, AUTO, and EXPLICIT. Each of these modes generates a different style of XML document, and in this short article, I aim to provide a simple overview of each method
An Introduction to XML Templates with SQL Server 2000
Denise E.
I've been working with XML templates more and more lately and I'm continuously amazed by how easy they are to use. This article covers what XML templates are and how to use them with SQL Server 2000
Converting an Access Database to SQL Server
Microsoft Team
The easiest way to convert an Access database to SQL Server is to use the Upsizing Wizard. The Upsizing Wizard
Moving a Database Diagram in SQL Server 7.0 and 2000
Microsoft Team
This step-by-step article describes how to move a database diagram that was created in SQL Server Enterprise Manager. Database diagrams are contained in the dtproperties system table of the database
Using the SQLDMO COM Object to Control SQL Server from ASP.NET
Shannon Horn
A key benefit brought about through graphical user interfaces (GUIs), namely Microsoft Windows, is the ability for applications to easily interact with each other. When applications interact with other applications, the entire operating system becomes more robust and easier to use. Furthermore, entire subsystems, suites, and extended applications can be developed by utilizing applications that interact with each other. Microsoft's solution to application interaction prior to the introduction of .NET was called the Component Object Model (COM)
SQL Server Disaster Recovery
Microsoft Team
This bulletin gives an introduction on how to go about developing a disaster recovery strategy for SQL Server. Also included is a case study demonstrating typical considerations when designing a disaster recovery strategy
Using DB-LIBRARY in a Threaded Application
Microsoft Team
When you are creating a DB-LIBRARY threaded application
Logging Data Transformation Services Package Execution to a Text File
Microsoft Team
SQL Server can log information about package execution and the detailed step level execution in a package to a text file. You can use the text log file to troubleshoot both package level and step level errors. This how-to article describes how you enable logging to a text file
Obtaining a List of DTS Packages
Microsoft Team
You can store the Data Transformation Services (DTS) package in one of three places. The following code samples describe the three methods to obtain a list of packages for each storage method
Data Transformation Services (DTS)
Microsoft Team
How to use an ActiveX script task to import data into a new Excel file Locking Down DTS
Brian Knight
By default, anyone with an account on your SQL Server can create a package and save it to your SQL Server. This doesn't present a problem if people are saving packages as .DTS files, but when people save them onto the SQL Server or Repository, your MSDB database can grow out of control. This quick tip shows you how to prevent people from seeing packages on your server and creating new ones
Meta Data & Knowledge Management: Managed Meta Data Environment: A Complete Walk-Through, Part 6
David Marco
This column is adapted from the book Universal Meta Data Models by David Marco & Michael Jennings (John Wiley & Sons). In the last several columns, I presented the six major components of a managed meta data environment (MME): meta data sourcing layer, meta data integration layer, meta data repository, meta data management layer, meta data marts and meta data delivery layer. This installment will discuss the MME's fourth component, the meta data management layer
The Essence of Meta Data: XML Standards are an Integral Part of Meta Data
R. Todd Stephens
One of the interesting questions that has been asked over the past few years is what role does meta data play in the development of XML? In the past, data architects could ignore the meta data aspects of defining data or, better said, they could let the application tool take care of mundane activities such as meta data. However, with the advent of XML technologies, meta data must be placed on the front end and integrated into every step of the process. In fact, data architects must find ways to exploit meta data and adapt their training to a new world where, perhaps, the definition of data is as important as the data itself. Take a look at Figure 1 and review the five tiers of XML asset creation
Data Transformation Services for SQL Server 2005-Frequently Asked Design Questions
Huntting Buckley, Donald Farmer, Amit Goyal, Craig Guyer, James Howey
This paper answers some of the questions most frequently asked about Data Transformation Services in SQL Server 2005. In particular it addresses some of the design issues–it is a "why" FAQ rather than a "how" FAQ. (6 printed pages)
Securing Your SQL Server 2005 Express Edition Server
William Vaughn
Get introduced to SQL Server Express, learn how to install and configure it in a secure manner, plus get information on the basics of SQL Server security. (15 printed pages)
How to Multiply Column Values in SQL Server
ExtremeExperts.com
We had a recent discussion on the public newsgroups on how to get this requirement in a single statement without using T-SQL. I was indeed taken by surprise how this requirement was answered by fellow MVP's. Infact there is a solution even though no so elegant, but works great having said what the requirement looks. So take the following code and you will find it for yourself
ROW_NUMBER in SQL Server 2005
ExtremeExperts.com
This is yet another cool feature in SQL Server Yukon .It has been in the SQL Server 2000 wish list for quite sometime. But I am excited to see this T-SQL enhancement make it into the SQL Server 2005 version for sure. What is this requirement? It is to generate a pseudo column as the query optimizer sends the output. This generated column can be used for various requirements and used in the UI layer for some identifier purposes. Lets take a sneak preview to this feature and look at some interesting implementation too
Introduction to SQL Server 2005 DTS
ExtremeExperts.com
DTS has come a long way in SQL Server 2005. The architecture of DTS has changed and transformed to the level where all users have to rethink the way DTS works from its previous versions. Nevertheless a more fresh perspective to understand this new version. I would surely consider that DTS has become more mature. And in this article we will take a quick tour to look at how a simple DTS Package can be created and used
Accessing FOR XML Output in SQL Server
ExtremeExperts.com
I was fascinated with the amount of features that SQL Server 2005 brought wrt to XML datatype and related features. In exploration into the same has been my latest work. And you get to see some of the interesting works on the same. This is a very common requirement that I felt is worth documenting too. In the present SQL Server 2000 world if we were to take the output of a FOR XML into a variable, it is next to impossible
Finding XML system objects in SQL Server 2005
ExtremeExperts.com
Having played with a couple of examples with XML features in Yukon, you might be interested to know where can I find the association of XML Schema's to the XML Datatype. In this FAQ section we will look at the tables that govern behind the scenes these values. It is to be understood that SQL Server fundamentally is a relational engine. Even though introducing XML structures to the database column has not removed its fundamental strength of looking them as relational structure
Scrollable DataGrid with Sorting
ExtremeExperts.com
This article is continuation to my previous article in msdn India on "Creating Scrollable DataGrid Web Server Control". In that article i have explained about how to create scrollable datagrid with fixed header. After reading this article, one of the frequent question from reader on that article was "how to enable default sorting in this type of datagrid". So in this article, i am going to explain how to enable sorting in scrollable Datagrid with fixed header
SQL Server Health and History Tool (SQLH2) Reports
Microsoft Corporation
The Microsoft SQL Server Health and History Tool (SQLH2) collects information from instances of SQL Server and stores this information. In order to view the information, we have created reports that you can download here. These reports require Microsoft's SQL Server Reporting Services to display
How to Perform a SQL Server Performance Audit
Brad M. McGehee
If you have been a DBA for long, then you will know that performance tuning SQL Server is not an exact science. And even if it were, it would still be difficult to find the "optimal" configuration for the "optimal" performance. This is because there are few absolutes when it comes to performance tuning. For example, while one particular performance-tuning tip may help boost performance one way, at the same time it might hurt performance in another way
SQL Server Configuration Performance Checklist
Brad M. McGehee
In this section, we are going to take a look at some of the performance-related SQL Server configuration settings. These are SQL Server-specific settings that can be modified using either Enterprise Manager or SP_CONFIGURE
SQL Server Database Settings Performance Checklist
Brad M. McGehee
As part of your performance audit, you need to examine each database located on your server and examine some basic database settings. When compared to some of our other performance audit tasks, you will find this audit one of the easiest. For convenience, you may want to consider photocopying a copy of the above chart, producing one copy for each database that you will be auditing
Operating System Performance Checklist
Brad M. McGehee
The focus of this section of our performance audit is the base operating system, and how to optimize it in order to get the best performance out of SQL Server
SQL Server Hardware Performance Checklist
Brad M. McGehee
From the previous section, on using Performance Monitor, you may have identified some potential hardware bottlenecks that are negatively affecting your SQL Server's performance. In this section, we will take a look at each of the major components of a SQL Server's hardware, and examine what can be done to help maximize the performance of your hardware
Using Performance Monitor to Identify SQL Server Hardware Bottlenecks
Brad M. McGehee
The best place to start your SQL Server performance audit is to begin with the Performance Monitor (System Monitor). By monitoring a few key counters over a 24 hour period, you should get a pretty good feel for any major hardware bottlenecks your SQL Server is experiencing
Microsoft SQL Server 2000: Troubleshooting Connectivity
Farooq Mahmud
Today in this WebCast we will be talking about troubleshooting Microsoft® SQL Server™ 2000 connectivity. Connectivity is a vast topic, so instead of diving into troubleshooting connectivity issues right away, it helps to have some background on the topic (slide 2). So we will begin the WebCast by talking about the basics of client-server communications. Then we will get into Net-Libraries. We'll then talk about client-server communications using the TCP/IP Net-Library in a TCP/IP network. These topics will provide the necessary foundation so that we can then talk about troubleshooting common connectivity issues. We'll conclude the WebCast by presenting connectivity-troubleshooting references
Getting Started with SMO in SQL 2005 - Restores
Jasper Smith
In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to perform restores using SMO in SQL2005. For information on how to build and compile a SMO application please review Getting Started with SMO in SQL 2005. For SQL 2005 Express Edition Beta 2 please review Workaround for compiling SMO applications with SQL 2005 Express Edition
Getting Started with SMO in SQL 2005 - Verifying Backups
Jasper Smith
In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to verify backups using SMO in SQL2005. For information on how to build and compile a SMO application please review Getting Started with SMO in SQL 2005. For SQL 2005 Express Edition Beta 2 please review Workaround for compiling SMO applications with SQL 2005 Express Edition
Getting Started with SMO in SQL 2005 - Integrity Checks
Jasper Smith
In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to examine database integrity using SMO in SQL2005. For information on how to build and compile a SMO application please review Getting Started with SMO in SQL 2005. For SQL 2005 Express Edition Beta 2 please review Workaround for compiling SMO applications with SQL 2005 Express Edition
Getting Started with SMO in SQL 2005
Jasper Smith
In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to build a SMO application and how to connect to a SQL Server and retrieve some server properties. Example code will use VB.NET and C# as well as VBScript. For those of you that don't have access to Visual Studio 2005 Beta 1 I'll include the command line compiler commands. It may be easier to get started using VBScript as this is the simplest way to develop a quick SMO application. I'd also highly recommend the SMO sample applications that are part of the SQL Server Engine samples that come with SQL2005 Beta 2 and are also available for download for Express users here
Getting Started with SMO in SQL 2005 - Backups
Jasper Smith
In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to perform backups using SMO in SQL2005. For information on how to build and compile a SMO application please review Getting Started with SMO in SQL 2005. For SQL 2005 Express Edition Beta 2 please review Workaround for compiling SMO applications with SQL 2005 Express Edition
Workaround for compiling SMO applications with SQL 2005 Express Edition
Jasper Smith
There is an issue with the setup of SQL Server 2005 Express Edition with regard to the SMO assemblies required to compile SMO applications. Note that this does not affect the ability to run SMO applications, merely to compile them. The issue is that the required SMO assemblies are only installed in the GAC and are not available to be referenced when compiling an SMO application. Whilst I'm sure there has to be an easier way, the method below worked fine for me and it's a one time operation
Automating Database maintenance in SQL 2005 Express Edition Part II
Jasper Smith
In this series of articles, I'll demonstrate a couple of different approaches to writing a maintenance utility that mimics some of the behavior of the sqlmaint utility that is included with SQL Server 2000. SQL Server 2005 Express Edition does not include such a utility so these articles will show how we can easily create one ourselves using either TSQL or SMO (SQL Management Objects - the successor of SQL-DMO). Rather than dive into the code, these articles will demonstrate how to use these utilities for backing up and maintaining your databases and how to schedule these tasks using the Scheduled Tasks facility in Windows XP and Windows 2003
Automating Database maintenance in SQL 2005 Express Edition Part I
Jasper Smith
In this series of articles, I'll demonstrate a couple of different approaches to writing a maintenance utility that mimics some of the behavior of the sqlmaint utility that is included with SQL Server 2000. SQL Server 2005 Express Edition does not include such a utility, so these articles will show how we can easily create one ourselves using either TSQL or SMO (SQL Management Objects - the successor to SQL-DMO). Rather than dive into the code, these articles will demonstrate how to use these utilities for backing up and maintaining your databases and how to schedule these tasks using the Scheduled Tasks facility in Windows XP and Windows 2003. In this article we will concentrate on the TSQL version of the utility which is in the form of a stored procedure - expressmaint
Exporting data to a XML file in SQL Server 2005
Jasper Smith
In this article, I'll demonstrate how we can combine the new CLR integration and FOR XML enhancements to easily generate xml files from our relational data. For an introduction to .NET Stored procedures have a look at Introducing CLR Stored Procedures in SQL Server 2005. This article will reference the Orders table in the Northwind database. You can download the demo databases (plus an abridged version of Books On Line and sample code) for SQL 2005 here
Introducing CLR Stored Procedures in SQL Server 2005
Jasper Smith
SQL Server 2005 introduces the ability to write Stored Procedures, Functions, Triggers, User Defined Types and User Defined Aggregations using any of the available .Net languages such as VB.NET or C#. This article will show you how to create a very simple "Hello World" stored procedure using VB.NET or C# to help introduce this feature and demonstrate how to load a .Net assembly into SQL Server 2005
Enabling client connections to SQL Server 2005 Express Edition
Jasper Smith
By default when you install SQL Server 2005 Express Edition, connections are only possible via the Shared Memory protocol on the same machine and only then by the new sqlcmd commandline tool ( the replacement for osql/isql). In order to connect from downlevel clients (such as Query Analyzer) or from another machine, you have to follow a number of steps detailed below. The walkthrough is for a default named instance called SQLEXPRESS
Examining DDL Triggers and Event Notifications in SQL Server 2005
Jasper Smith
Originally presented at the PASS SQL Server Summer Academy 2004, this presentation demonstrates the use of DDL Triggers and Event Notifications in SQL Server 2005 and their use as an audit and change tracking mechanism. Code samples are included in the downloadable zip file. The code samples have been updated for SQL Server 2005 Beta 2
Granting execute permissions to all stored procedures in a database
Jasper Smith
Whilst SQL Server 2000 has fixed database roles such as db_datareader and db_datawriter that allow a user read or write access respectively to all the table is a database, no such role exists for the execution of stored procedures (a db_executor role if you will). This article describes how to grant execute permission to all stored procedures in a database to a specific user or role in both SQL2000 and SQL2005
Beginners guide to accessing SQL Server through C#
Matt Newman
In this article I plan to demonstrate how to insert and read data from a SQL Server or MSDE database. This code should work on both SQL Server , I am using 2000, and MSDE. I am using Visual Studio 2002, but this should work with Visual Studio 2003, Web Matrix, and the command line SDK. This code should work with both C# applications and C# web applications and webservices. This code does not compile on the FreeBSD with Rotor
FAQ: How to connect to SQL Express from "downlevel clients"(Access 2003, VS 2003, VB 6, etc(basically anything that is not using .Net 2.0 or the new SQL Native Client))
sqlexpress's WebLog
This is the short version, the longer version is further down, also make sure and review the SQL Express BOL and the Mini BOL
Miscellaneous SQL Server Performance Tuning Tips
sql-server-performance.com
If you need to delete all the rows in a table, don't use DELETE to delete them all, as the DELETE statement is a logged operation and can take time. To perform the same task much faster, use the TRUNCATE TABLE instead, which is not a logged operation. Besides deleting all of the records in a table, this command will also reset the seed of any IDENTITY column back to its original value
Extracting a String Within Delimeters - Part 2
Stephen Lasham
I recently received correspondence from Denis Oliynik, IT manager at JSC 'Rise', Kiev in the Ukraine, who kindly provided me with a script improving on my previously published solution. Denis's solution removes the CASE statement for handling where a second delimiting character is not present. This simplified code explained below offers a clever trick to add to your SQL skills

[В начало]

ФОРУМ SQL.RU

Самые популярные темы недели

Кто на чем пишет клиентов под SQL Server?
Суррогатные или естественные
Индексы и Where
Рыба есть! О контроле пересоздания объектов
Заметное падение производительности
Линк к гадскому ORACLE... Выполнение "его" процедур?!?
DRI & Cascade operations VS Triggers: что лучше?
Как оптимизировать индекс?
Temp table
SOS! Как запустить отладчик SQL кода?
Как испортить БД ?
Вопрос по триггерам "instead of update"
Проблемы с репликацией
А есть ли аналог DECODE из оракла в MSSQL
Как включить использование hyper-threading в MSSQL2000?
Прошу помощи с запросом
Помогите выбрать толковую книгу по SQL
помогите начинающему? Замена данных запросом
как вызвать хранимую процедуру
Построение запроса

[В начало]

Вопросы остались без ответа

Не могу составить запрос
Как перепедключится к СКЛ
Триггер неправильно работает....
Как скопировать роль в туже БД с другим именем
Как скопировать роль со всеми правами в ту же БД с другим именем роли для последующего редактирования прав в новой роли ?
DBCC SHRINKFILE скорость
freetds to SQLSERVER
Значение по умолчанию
DiffGram в Yukon-е.
При работе DTS выдает ошибку exeption_access_violation
репликация

[В начало]

#214<<  #215