SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |
   
#340<<  #341  >>#342

СОДЕРЖАНИЕ

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


СТАТЬИ

Основы ввода-вывода Microsoft SQL Server. Часть 2 (продолжение)

По материалам статьи Bob Dorr: Microsoft SQL Server I/O Basics. Chapter 2

Большие журналы транзакций

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

WHILE(@I < 10000) BEGIN BEGIN TRAN INSERT INTO tblTest values ('A', @I) COMMIT TRAN SET @I = @I + 1 END - vs - BEGIN TRAN WHILE(@I < 10000) BEGIN INSERT INTO tblTest values ('A', @I) SET @I = @I + 1 END COMMIT TRAN

Во время исполнения первого скрипта SQL Server сделает запись в файл журнала регистрации транзакций 10000 раз, как этого требует используемая конструкция BEGIN TRAN … COMMIT TRAN. Во втором скрипте SQL Server упаковывает транзакции всех 10000 вставок в одну транзакцию и завершает все транзакции одновременно.
Использование журнала транзакций для обоих примеров даёт следующие приближения:

  • При использовании для первого примера размера сектора 4КБ, используются ~40МБ дискового пространства.

  • При использовании для первого примера размера сектора 512Б, используются ~5МБ дискового пространства.

  • При использовании для второго примера размера сектора 4КБ, используется ~1МБ дискового пространства.

  • При использовании для второго примера размера сектора 512Б, используется ~1МБ дискового пространства.

Если подсистемы ввода-вывода сообщает о том, что размер сектора больше 512Б, это означает, что размер занимаемого файлом журнала транзакций SQL Server физического места будет зависеть от коэффициента: "Rate = n / 512", где "n" - это новый размер сектора. Дизайн прикладной части может оказаться критически важным для эффективного использования занимаемого журналом места.
Обратите внимание, что нужно быть очень осторожным при увеличении длинны транзакции, поскольку это может негативно сказаться на параллелизме из-за того, что блокировки будут удерживаться до завершения обрамляющей транзакции.
Хотя база tempdb изначально не журналируется, внутренние операции, такие как распределение страниц данных, могут журналироваться. Поэтому, большие размеры сектора могут повлиять и на размер tempdb тоже.

Восстановление и присоединение

База данных SQL Server может быть восстановлена или присоединена на системе, у которой размер сектора меньше, чем на изначальной системе. Для гарантии целостности данных, размер большого сектора должен без остатка делиться на размер меньшего сектора. Например, если у источника размер сектора был 4КБ, а восстановление/присоединение было выполнено на системе с размером сектора 1КБ или 512Б, эти размеры делят исходный без остатка. Восстановление или присоединение на системе с меньшим размером сектора равным 1536Б не делит изначальный размер сектора без остатка и потребует немедленного выполнения операций перезаписи секторов.
В SQL Server не предусмотрена возможность динамической подгонки базы данных под сектор большего размера. SQL Server просто отказывается восстанавливать или присоединять базы данных на системах с большим, чем на источнике, размером сектора; при этом генерируется сообщение об ошибке, а сами операции восстановления или присоединения прерываются. Если бы можно было работать с базой данных на диске, отформатированном с меньшим размером сектора, чем он был изначально, это привело бы к нарушению протокола WAL, потому что изменение размера сектора не гарантирует правильного размещения записей журнала в физических размерах сектора и записи регистрации транзакций будут перезаписаны.
На момент публикации этой статьи, некоторые из имеющихся подсистем ввода-вывода сообщали о том, что размер сектора большие 512Б, но большинство таких систем этого не делали. Увеличение размера сектора является следствием появления новых технологий и требует внесения изменений в программные интерфейсы уровня операционной системы. Новые версии Microsoft ® Windows и SQL Server правильно поддерживают большие размеры сектора и учитывают динамическую подстройку размера сектора.

Формат работы с секторами 4 КБ на системах с меньшим размером сектора

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

Системные и учебные базы данных

В поставку SQL Server 2005 входят системные (master, model и msdb) и учебные базы данных, которые были созданы на отформатированных с размером сектора 4КБ системах, и поэтому они могут быть установлены на дисках с размером сектора до 4КБ. Системные и учебные базы данных SQL Server 2000 создавались на секторах 512Б, и поэтому их установка на дисках с большими секторами потерпит неудачу.
База данных model используется SQL Server как шаблон для создания tempdb и пользовательских баз данных. При этом используется только содержимое базы данных model, а не полный физический формат. База данных tempdb и новые пользовательские базы при создании используют тот размер сектора, который во время создания сообщает операционная система. Определение размера сектора выполняется пофайлово. Это может привести к расхождениям в размере сектора у разных файлов базы данных. Удостоверьтесь, что размеры секторов имеющихся путей ввода-вывода равны.

Определение отформатированного размера сектора базы данных

Выполните команду DBCC FILEHEADER ('«dbname»'), которая показывает заданный при форматировании размер сектора для каждого файла базы данных. Установленный при форматировании размер сектора в байтах показывает столбец SectorSize.

Какие размеры сектора поддерживает SQL Server?

В настоящее время SQL Server поддерживает следующие размеры сектора:

  • физические размеры сектора, которые делят 4КБ без остатка;

  • физические размеры сектора, которые меньше оригинального размера у базы данных; и больший, оригинальный размер сектора делится без остатка на меньший, новый размер сектора;

  • 512;

  • 1024;

  • 2048;

  • 4096.

ПРОДОЛЖЕНИЕ СЛЕДУЕТ

[В начало]

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

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

Работа с базами данных в Delphi с помощью ADO
delphisources
В этой статье только самый минимум сведений, "чтоб работало"... Берем Microsoft Access, делаем базу данных (этот процесс я описывать не буду, так как, во-первых, каждый умеет, а во-вторых, у меня нет Accessa:). В общем, допустим, что у нас имеется база данных kor.mdb из пяти таблиц. Нам надо вывести одну из них в форму. Итак, ставим на форму компонент ADOConnection с вкладки ADO палитры компонентов, дважды кликаем по свойству ConnectionString появляется окно 'Form1.ADOConnection1 ConnectionString', выбираем второй вариант (Use Connection String) и нажимаем кнопку Build...

Поддержка XML в SQL Server 2000
John Papa
В SQL Server 2000 имеется ряд функций поддержки XML, позволяющих преобразовывать реляционные наборы данных в иерархические XML-документы, читать XML-документы и выполнять массовую загрузку данных из XML-документов. Например, можно передать XML-документ хранимой процедуре, объединить XML-данные с какими-либо таблицами и возвратить набор записей или даже изменить данные в базе. Введение функции OPENXML и раздела FOR XML отражает тот факт, что роль XML в современных системах уровня предприятия постоянно растет. Благодаря этим средствам SQL Server не только поддерживает XML, но и может более эффективно выполнять массовую загрузку данных.

Целостность реляционных данных
Dr. dimdim
Во второй части реляционной модели данных определяются два ограничения, которые должны выполняться в любой реляционной базе данных. Это:

"Умный" SQL
Павел Леченко
Многие программисты, работающие с microsoft sql server, предпочитают писать логику приложения в коде своих программ, используя sql server только как хранилище данных. Одна из причин такого подхода - обычная лень (например, при переносе базы данных с access на sql server), другая же причина - мнение, что язык sql не столь гибок, как delphi, c++ и другие языки программирования. Но ведь он и не предназначен для работы с конечным пользователем! На самом деле - sql предоставляет необходимый минимум возможностей для работы с данными; надо только ими правильно воспользоваться. В этой статье я хочу поделиться некоторыми хитростями работы с microsoft sql server, которые я использую в своих проектах. Сразу оговорюсь, что все скрипты написаны для ms sql server 7.0.

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

[В начало]

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

Managing Space Used by Objects
Microsoft
Index Allocation Map (IAM) pages map the extents in a database file used by a heap or index. IAM pages also map the extents allocated to the ntext, text, and image page chain for any table that has columns of these types. Each of these objects has a chain of one or more IAM pages recording all the extents allocated to it. Each object has at least one IAM for each file on which it has extents. They may have more than one IAM on a file if the range of the extents on the file allocated to the object exceeds the range that an IAM can record.

The easy fix to mysterious SQL collation errors
Jonathan Rabson
I'm doing this query that's the same SELECT statement for two tables with a UNION ALL connecting them. While each query works great on its own, when I try to run them together (with the UNION ALL) I get this error message:

Microsoft Exchange Server Jetstress Tool (64 bit)
Brief Description
Simulate disk I/O load on a test server running Exchange to verify the performance and stability of your disk subsystem before putting your server into a production environment. Use Jetstress to verify the performance and stability of a disk subsystem prior to putting an Exchange server into production. Jetstress helps verify disk performance by simulating Exchange disk Input/Output (I/O) load. Specifically, Jetstress simulates the Exchange database and log file loads produced by a specific number of users. You use Performance Monitor, Event Viewer, and ESEUTIL in conjunction with Jetstress to verify that your disk subsystem meets or exceeds the performance criteria you establish. After a successful completion of the Jetstress Disk Performance and Stress Tests in a non-production environment, you will have ensured that your Exchange disk subsystem is adequately sized (in terms of performance criteria you establish) for the user count and user profiles you have established. It is highly recommended that the Jetstress user read through the tool documentation before using the tool.

Using the OBJECT_ID function to improve concurrency
Glenn Berry
If you are in the habit of querying system tables (like sysobjects) in your TSQL code, you might want to switch to using the built-in OBJECT_ID function, like you see below.

Capture the reporting benefits of database snapshots in SQL Server 2005
Tim Chapman
Database snapshots are yet another great new feature in SQL Server 2005. A database snapshot is a read-only copy of a database that reflects all database data up to the point in time for which the snapshot is taken.

Put to the Test: Microsoft Report Builder
Cindi Howson
This ad hoc query tool is a free component of SQL Server 2005 and part of Microsoft's evolution from providing infrastructure to offering a complete BI platform.

Forced Parameterization in SQL Server 2005
Brad M. McGehee
One of the main benefits of using a stored procedure to execute Transact-SQL code is that once a stored procedure is compiled and executed the first time, the query plan is cached by SQL Server. So the next time the same stored procedure is run (assuming the same connection parameters are used), SQL Server does not have to recompile the stored procedure again, instead reusing the query plan created during the first compilation of the stored procedure. If the same stored procedure is called over and over again, with the query plan being reused each time, this can help reduce the burden on SQL Server's resources, boosting its overall performance.

Using CLR integration to compress BLOBs/CLOBs in SQL Server 2005
yolousa72
This article shows how to use CLR integration to compress data in SQL Server 2005. Manipulating Binary Large Objects (BLOBs) and Character Large Objects (CLOBs) has always been difficult in SQL Server. The new SQL Server 2005 provides new data types (NVARCHAR(MAX), VARCHAR(MAX) and VARBINARY(MAX)) for large object storage (up to 2 GB) allowing better manipulation as well as the ability to process these data types using CLR procedures and functions. This article shows how to create CLR functions to seamlessly compress and decompress large data objects with minimum performance impact using compression algorithms included in.NET Framework 2.0. Code samples included in this article can be used in any database implementation but do not cover all possible scenarios. For large implementations or mission critical applications consider using third party products like SQLCompres.NET (it is free).

Integrating .NET Code and SQL Server Reporting Services
Rod Paddock
SQL Server Reporting Services versions 2000 and 2005 (SSRS) has many powerful features. SSRS has a well-designed data access engine, a great set of layout tools, and an excellent expression system for creating complex formulas. While the expression system is quite powerful it is not suitable for all applications. This is where SSRS shines. SSRS gives developers the ability to add custom code to their report layouts. This article demonstrates adding custom code to SQL Server Reporting Services reports.

Sql Server Full-Text Search Protips Part 1: Setup
Wyatt Barnett
As I have previously mentioned, Sql Server's Full-Text search can let a developer create some very slick features disturbingly easily. And, unlike many other Full-Text implementations, it is not limited to plain text fields. It can also search within binary fields with the proper setup considerations. Needless to say, there are a few protips to making Full-Text indexes work and taking advantage of them. In this post, I will tell you how to get Full-Tex

Sql Server Full-Text Search Protips Part 2: CONTAINS vs. FREETEXT
Wyatt Barnett
In Part 1 of this series we explored the methodology for enabling a Sql Server database for Full-Text Indexing and enabling Full-Text indicies on specific tables. But now we get to the good part-how to use that index in your application.

Sql Server Full-Text Search Protips Part 3: Getting RANKed
Wyatt Barnett
In Part 1 of this series we explored the methodology for enabling a Sql Server database for Full-Text Indexing and enabling Full-Text indices on specific tables. And in Part 2 of this series we explored the methodology for accessing these Full-Text indices using the CONTAINS and FREETEXT functions. In this final part of the series we will examine the usage of the CONTAINSTABLE and FREETEXTTABLE functions to provide ranked search results to the calling application.

Build Your Own Analysis Services Cache-Warmer in Integration Services
Chris Webb
Cache-warming is one of the most neglected performance-tuning techniques for Analysis Services: perhaps it seems too much like cheating? Yet almost everyone knows how much difference there can be executing a query on a cold cache and a warm cache so there should be no excuse not to be doing it, especially if you know what queries your users are likely to be running in advance. AS2005's caching mechanism is more complex than I can describe here (or than I can describe full stop - although I hear that the recently published "Microsoft Analysis Services 2005" has some great information on this front) but a lot of the time it can cache raw data of the cube and quite often the results of calculations too; you'll need to test your own cubes and queries to find out exactly how much you'll benefit but almost every cube benefits to a noticeable extent.

Variables to the rescue
Gert Drapers
Do you have a need to make parts of your build conditional or environment dependent? Variables are here to help you.

Q and A: Ratio between AWE Allocated and VM Committed/Reserved in DBCC MEMORYSTATUS output
Slava Oks
Q: What kind of ratio should you expect to see between AWE Allocated and VM Committed/Reserved? We are running 8GB with a cap of 7GB in the max server memory and yet the VM Committed is around 100MB. I was first shocked to see that the sqlservice in task manager only shows that SQL is using around 100MB and now that I have used MEMORYSTATUS I'm even more troubled.Does this mean that our server is starved for memory even though AWE can see almost all of the 7GB? On a side note we are seeing very high CPU utilization and my trail has come to a potential memory issue.

Case-insensitive Search Operations
SQL ISV PM Team
Many applications have a functional requirement for the underlying database to have a case-sensitive sort-order, implying that all character data related operations are case-sensitive. With SQL Server, such databases are created with either the 'binary' or any of the case-sensitive (CS) sort orders (collations). Often times these applications also need to perform case-insensitive searches for character data. This can be challenging and, if not implemented correctly, lead to sub-optimal performance, given that the database was created to be case-sensitive. Let's take a look at this using a simple example.

Vardecimal Storage Format and its implications on Backup/Recovery
Sunil Agarwal
Has any one tried restoring or attaching a SQL Server 2005 database on SQL Server 2000? You will find that SQL Server 2000 will fail this restore or attach. The reason is simple. SQL Server 2000 does not understand the physical structure changes in SQL Server 2005 database. SQL Server detects this incompatibility using the database version stored in the bootpage of the database. You cannot attach/restore a database with higher database version on a SQL Server that does not support it. Note that it is always possible to attach/restore a SQL Server 2000 database to SQL Server 2005. In fact, this must be allowed as otherwise customers will not be able to upgrade their database to run with newer SQL Server versions. In this case, the SQL Server 2005 understands the physical structures of SQL Server 2000 database and converts them to SQL Server 2005 specific structures during the upgrade process.

Boundary conditions for enabling vardecimal storage format
Sunil Agarwal
Have you ever tried updating a variable length column and fail? Well, it can happen if the modified row cannot fit on the page. One simple example of this as follows

Data row before and after vardecimal storage format
Sunil Agarwal
Paul Randal in one of his earlier BLOGs described DBCC Page paul-tells-all and the record layout. I thought it will be interesting to show how a row looks before and after the Vardecimal storage format is enabled. So here it is

Read-Only filegroups and Locking
Sunil Agarwal
I recently saw a newsgroup post that sort of implied that accessing data residing on read-only filegroups can save you on locking. Well, it could, but it does not. If you really want to save on locks, the whole database needs to be set to read-only.

BACKUP DATABASE 5x quicker using BLOCKSIZE option
Tony Rogerson
Got a very strange one on HP kick and its common across the 3 servers I have here.; might be useful for other kit - worth checking!

SQL Server Temporary Tables Versus Table Variables
Ben Forta
Temporary tables have long been an integral part of any complex SQL processing. If you have a long script or stored procedure, one which needs to extract or manipulate data using multiple queries and related statements, then being able to save results into temporary tables can dramatically improve performance (as well as readability and management). SQL Server has supported temporary table use for any years, I first used them in SQL Server 6.5 (which were released in the mid 90s). Temporary tables are created and used just like any other tables, and then can be either local or global. Here is a simple code snippet that creates two temporary tables, one local and one global: -- Create local temporary table CREATE TABLE #MyTempTable ( id INT PRIMARY KEY, firstName CHAR(50), lastName CHAR(50) ) -- Create global temporary table CREATE TABLE ##MyTempTable ( id INT PRIMARY KEY, firstName CHAR(50), lastName CHAR(50) ) The two code snippets look similar, the only difference is that one table is named #MyTempTable and the other is named ##MyTempTable. In SQL Server, a single # is used to refer to a local temporary table, and double # is used to refer to a global temporary table. Once created, these tables can be used like any other tables. You can SELECT, INSERT, UPDATE and DELETE, you can use them in JOINs and subqueries, and more. You just have to be careful to use # (or ##) as part of the table name. Oh, and you can also use SELECT INTO to create and populate a temporary table in one step. You can also DROP temporary tables. Although SQL Server can automatically drop the temporary tables for you, too. Temporary tables are automatically dropped when the session that created them terminates (goes out of scope). SQL Server 2000 added support for an alternative to temporary tables, the table variable. Table variables are similar to temporary tables in that they are intended for temporarily working with data. Here is how one is created: -- Create table variable DECLARE @MyTempTable TABLE ( id INT PRIMARY KEY, firstName CHAR(50), lastName CHAR(50) ) DECLARE is used to declare variables, and the variable is prefixed by @ (instead of #). In SQL Server, variables are always prefixed by @, and table variables are no different. Once created, a table variable can also be used like a regular table. You can SELECT, you can INSERT, UPDATE, and DELETE, you can use the table variable in JOINs and subqueries, and more. And the table variable has some important benefits over temporary tables:

Setting Up SQL Server 2005 Full-Text Searching
Ben Forta
SQL Server 2005 features an integrated full-text search engine, which several people have e-mailed me to ask about recently. (ColdFusion users can use <cfquery> to perform queries using SQL Server full-text searches). If you want to play with SQL Server 2005 full-text search capabilities, you need to do the following: 1) Once a database has been created, support for full-text must be enabled before any full-text operations can be performed.

Performing SQL Server FREETEXT Searches
Ben Forta
FREETEXT provides a simple mechanism by which to perform SQL Server 2005 full-text searches, matching by meaning as opposed to exact text match. Here is a simple example:

Performing SQL Server CONTAINS Searches
Ben Forta
SQL Server 2005 supports two forms of full-text search, FREETEXT and CONTAINS. CONTAINS is used to search for rows that contain words, phrases, partial phrases, words with the same stem, proximity searches, synonyms (using a thesaurus lookup), and more.

Ranking SQL Server 2005 Full-Text Search Results
Ben Forta
When performing full-text searches you usually want not just results, but a ranking indicating how close a match is to what you are looking for. In SQL Server 2005, ranks are accessed via ranking functions - FULLTEXT searches are ranked using function FULLTEXTTABLE() and CONTAINS searches are ranked using function CONTAINSTABLE(). Both of these functions are used the same way, and both accept search patterns, the same search patterns supported by the FULLTEXT and CONTAINS predicates themselves.

MDM Master Data Management Hub Architecture
Roger Wolter
MDM Master Data Management Hub Architecture. I'm doing a presentation on Master Data Management (MDM) in February so I'm going to do another series of architecture articles like I did for Service Broker but this time for MDM. I will start with the architecture of the database that stores the master data for the system. If you need a basic introduction to what MDM is, you can find one here.

SYSK 257: .NET way to analyze SQL data I/O performance
Irena Kennedy
Would you like to know how many server round trips it took to retrieve your data using ADO.NET? Would about the number of bytes sent and received? Total connection time? Command execution time? Number of returned rows?

SYSK 261: Investigating Blocked Processes in SQL Server
Irena Kennedy
Imagine, you execute who_is_blocked stored procedure and get a list of the blocked resources, the databases involved, the requesting and the blocking session ids, and the request mode. With that information, you can choose to terminate the blocking session, or otherwise handle the issue… Thanks to T. Davidson who wrote the stored procedure below, it's just that easy!

Essential SQL Server Date and Time Functions
Jeff Smith
Part I: Standard Date and Time Functions. I've posted some variations of these before, but here they all are in 1 easy package: The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Sending messages: reentrancy vs. deadlocks
Joe Duffy
Deadlocks aren't always because you've taken locks in the wrong order. In many systems, tasks communicate with other tasks through shared buffers. In a concurrent shared memory system, these buffers might be simple queues shared between many threads. In COM and Windows GUI programs these buffers might take the form of a window's message queue. In any case, if some task A performs a synchronous message send to task B, and task B does a synchronous send to task A, near simultaneously, and if neither task continues to process incoming messages, both will be blocked forever.

Plan Cache Concepts Explained
Sangeetha Shekar
Since the release of SQL Server 2005 there have been several questions around how plan caching has been implemented in this release and how to diagnose (and distinguish) plan cache related performance problems. In a series of blog articles we will attempt to address many of these questions. Lists of topics that will be covered in these blog articles are as below:

1.0 Structure of the Plan Cache and Types of Cached Objects
Sangeetha Shekar
The plan cache is comprised of 4 separate cache stores: Object Plans (CACHESTORE_OBJCP), SQL Plans (CACHESTORE_SQLCP), Bound Trees (CACHESTORE_PHDR), and Extended Stored Procedures (CACHESTORE_XPROC). Each of these four cache stores conform to a uniform caching policy with respect to costing and removal of entries. Within each cache store is a hash table that is divided into hash buckets. Each hash bucket may contain one or more cached entries. The hash value of all cache entries is computed as (object_id * database_id) mod (hash table size), and this specifies the hash bucket. A cache key is used to find an exact match for a plan in the cache store hash table bucket.

SSAS: New ASSP Function - ListFunctions()
Darren Gosbell
I just added another sample to the Analysis Services Stored Procedure project. The following text is copied from the Wiki documentation which I just updated. This function is not currently available as part of a compiled release, but it is available from the source code repository, so you can download the code and compile it yourself. I also have a Powershell based variation of this proc which was actually how I prototyped the technique. I plan to polish up the rough edges of that script and post it here soon too.

Dynamic SQL & SQL injection
Raul Garcia
I know there are a lot of papers that talk about dynamic SQL in more depth than what I am going to cover, but as SQL injection is still one of the biggest security problems in the relation databases world, that I decided to include this part as a quick (and hopefully helpful) reminder.

Improving SMO Performance - Two Hours down to Six Minutes
Allen White
I've finalized the programs I built to automatically rebuild my database maintenance jobs. One of the programs rebuilds the database integrity checks and optimizations jobs, and the other rebuilds the backup jobs. The process of rebuilding the optimizations jobs involves issuing this command for every index in every database:

Error handling in SQL Server 2005
Muthusamy Anantha Kumar
In SQL Server 2005, error handling was made easy. This article illustrates various methods of handling errors using the TRY and CATCH command and various ERROR_ functions.

Using SQL for IIS Logs Part 2
Don Schlichting
The previous article began with the benefits of utilizing Microsoft SQL Server for Internet Information Service (IIS) web log analysis. While there are many third party packages on the market for web log analysis, sometimes you may need highly specific information derived from the logs, in these cases, SQL Server is a very good choice as a data repository and query engine. ODBC logging was investigated as a means of moving IIS log data into SQL Server. Although this method is easy to setup, it has two disadvantages that must be evaluated to your particular web server environment to determine its usefulness. One disadvantage is the added processing overhead ODBC logging creates. The other disadvantage is that the columns ODBC can transport to SQL Server are fixed and cannot be altered. As an example, two of the columns that are missing from the ODBC method include the "referrer" and "cookie" fields. So if the data from these columns are required for your analysis, then ODBC logging cannot be used. This article will begin by examining DTS and Data Import as methods for moving IIS web logs into SQL Server.

Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
William Pearson
In IsSibling(): Conditional Logic within Calculations, another article within my MDX Essentials series, we introduced the IsSibling() function, from the perspective of its use within a calculation. We discussed the straightforward purpose of this logical function, to return a value indicating whether or not a member we specify is the sibling of another member we specify. (By "sibling," of course, we mean that the two members share the same parent.) We discussed the manner in which IsSibling() manages to do this, and ways we can leverage the operator to support effective conditional logic to meet various business needs within our own environments.

Calculating Days of the Week and Accounting Months 5-4-4
Gregory A. Larsen
My last article related to date calculations, which was written a few years ago, has generated a number of questions from readers. More than one reader has asked me if I knew how to calculate a certain day of the week based on the current date. Therefore, I have decided to write another article that will include a number of new date calculations related to days of the week. In addition, one of my readers provided me with the code for a specific date calculation related to Account Months 5-4-4, so I'm going to share that with you as well. Prior to discussing my different date examples, we need to discuss some of the SQL Server date functions that are useful when working with date fields.

Pop Rivett and the Suspect Database
Pop Rivett
Blake Savage gazed in horror at the string of errors in the database activity log of his communications module. It was always at the worst possible moment that SQL Server grenaded itself and produced a corrupt database. Without the database data, all off-planet communications were impossible! How were they to get back to their ship?

Refactoring Databases: The Process
Scott W. Ambler and Pramod J. Sadalage
This article is excerpted from Chapter 3 of the book titled, 'Refactoring Databases: Evolutionary Database Design', authored by Scott Ambler and Pramod Sadalage. Published by Addison-Wesley Professional in March, 2006, ISBN 0-321-29353-3, Copyright 2006. Please visit www.awprofessional.com/title/0321293533 for further information, including a Table of Contents.

Populating Fact Tables
Vincent Rainardi
Part 3 of Vincent Rainardi's data warehousing series looks at some of the situations and challenges of loading a fact table in a data warehouse.

Using Synonyms in SQL Server 2005
Mike Gunderloy
If you've been developing with SQL Server for any length of time, you're undoubtedly used to referring to objects with four-part identifiers:

Eliminating Tape
Tim OPry
In response to an editorial on eliminating tape in favor of disk backups, new author Tim OPry brings us a look at his environment and the process by which they eliminated the need for tape in their backup scheme while maintaining a well thought out disaster plan.

Table Defaultsa
Andy Warren
In working on an existing database, DBAs often look to normalize tables and correct obvious flaws in design. Recently Andy Warren was working on a consulting project and realized that defaults would prevent many issues.

A Lap Around SQL Server 2005 Compact Edition
Michael Jones
With a new name, new capabilities, and a new focus, the product formerly called SQL Server Mobile Edition expands its reach to small-footprint desktop applications-and it's free! On April 6 of this year, Microsoft announced that SQL Server Mobile Edition (SQL Server Mobile) would be replaced later this year with Microsoft SQL Server 2005 Everywhere Edition (SQL Server Everywhere). Later, in early November, Microsoft announced that it would rename the product again to SQL Server Compact Edition (SQLce). As of this writing, SQL Server Everywhere (the download page has not been updated with the new name) has been released as a CTP on Microsoft's Web site.

Understanding Object Ownership
Kathi Kellenberger
In SQL Server 2005, object ownership will have a different meaning, but since most of us work with SQL Server 2000, Kathi Kellenberger has written a good introduction to understanding how object ownership works and why you might want to follow certain practices.

SQL Server 2005 Integration Services - Part 39 - Send Mail Task
Marcin Policht
Following presentation of the most popular Data Flow transformations in SQL Server 2005 Integration Services, we are revisiting the topic of Control Flow tasks, in order to make our coverage more complete. Among the most common tasks that have not yet been discussed in our series is the Send Mail task, which will be the topic of this article. In addition to describing its characteristics, we will also explore similar functionality built into Database Engine, focusing on its benefits and its ability to leverage them in SSIS packages.

Indexing in SQL Server 2005
Aaron Ingold
How should you approach indexing your database? Do you have a good strategy for deciding which indicies to create and on which columns? Aaron Ingold brings us a great article discussing these very topics that is likely to become a must read for many DBAs.

First Look: SQL Server 2005 SP2 CTP
Wayne Fillis
Service Pack 2 for SQL Server 2005 is not quite here, but Wayne Fillis brings us a look at what you can expect if you install the CTP version that is available now.

Server Side Paging using SQL Server 2005
graz
A common activity in applications is to page results or record sets from a database. This is usually done on the client using the client's paging functionality or on the server through a variety of methods. In SQL Server 2000 those server side methods typically used dynamic SQL or nested TOP clauses and weren't very efficient. Using Common Table Expressions in SQL Server 2005 we have a better way to page record sets on the server.

Using SQLCLR functions in indexed views
Bob Beauchemin
It's been over a month since I've blogged, confirmed by the previous blog entry when "it's sunny in Barcelona". Thought I'd blog about a question that someone wrote to ask me a few weeks ago.

Does sp_dropuser "do the right thing" with schemas?
Bob Beauchemin
Here's another blog posting to answer a question from over a month ago.

Some questions and answers on plan guides
Bob Beauchemin
Thought I'd answer one last question or two from the last time I did the talk on SQL Server 2005 plan guides. If you're asking at this point "what is a plan guide", reference the SQL Server 2005 BOL or this blog entry.

Distributed Keys and Disjoint Subtypes
David Portas
Entity subtyping is a very common data modelling scenario that gets extensive coverage in books on relational design but it isn't necessarily well supported by SQL DBMSs. The following is a simple technique I have found useful for implementing some kinds of entity subtypes. Specifically it applies to the case of Disjoint Subtypes that require what Hugh Darwen calls a Distributed Key - a uniqueness constraint spanning multiple tables.

Code Generation in T-SQL
James Green
OK, so I was trying to kick this Code Generation bent I have been on of late, but… opportunity knocked yesterday for a query where one of the possible solutions involved codegen and I was weak :-)

[В начало]

ФОРУМ SQL.RU

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

Microsoft SQL Server. Полезные алгоритмы от SQL.RU (+CD)
как защитить свои разработки ?
Установка SQL server
С Новым Годом!
Не могу установить SQL Server 2000 все перепробовал !!! Help!
Как сделать выборку, если имя таблицы определяется в этом же запросе?
Глюк при создании базы dbo не присваивается логин sa
Как организовать подсчет веток в дереве?
MSSQL Server 2000 привязка с Web-сайтом
Сумма вычисляемых полей - хоть стой, хоть падай
Знатокам про трафик
Как лучше распорядиться дисковым пространством?
SQL 2005 Express Edition и dts
Падает сервер 2005
Помогите с DeadLock-ом разобраться.
Вопрос
Проблемы с правами доступа
Поломались все индексированные view
update поле типа image(16)
выполнение скриптов

[В начало]

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

И снова репликация глючит ((
Удаление подписки
Job & Notification
ещё один EXCEPTION_ACCESS_VIOLATION
Проблемы с MS SQL Server 2000 MSDE
Отчет по расписанию.
Утечка памяти в КПК
MS SQL 2005 + 1C: УПП v.8.0... Помощи прошу!
не могу подконнектиться к MSSQL 2005
Поиск флективных форм слов, расположенных рядом

[В начало]

#340<<  #341  >>#342
Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013