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

СОДЕРЖАНИЕ

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

 

СТАТЬИ

Доступ к открытой только для чтения базе из нескольких экземпляров

В SQL Server 2005 была введена поддержка масштабирования доступа к общедоступным (далее общим) базам данных (Scalable Shared Database - SSD), т.е. одну и ту же открытую только для чтения базу данных могут использовать несколько экземпляров SQL Server, при этом, файлы такой базы размещаются на томах с атрибутом "read-only". Подробную информацию об установке и обслуживании SSD можно найти в SQL Server 2005 Books Online Web Refresh, а также в статье Microsoft Knowledge Base: Scalable shared databases are supported by SQL Server 2005.
SSD позволяет использовать несколько новых возможностей масштабирования, в числе которых можно назвать следующие:

  • доступ для множества серверов;

  • разнесение серверных ресурсов;

  • разнесение ресурсов системной базы данных tempdb.

Обратите внимание: доступ к открытым только для чтения файлам баз данных SQL Server никогда не поддерживался для физически других серверов. SQL Server SSD ни в каком виде не поддерживает запись в файлы базы данных.

Некоторые подсистемы ввода-вывода умеют на уровне тома налету копировать запросы на запись, (copy-on-write snapshots, COW) поддерживая, таким образом, моментальный снимок файла. В подобных решениях, подсистемы ввода-вывода контролируют операции записи на первичном томе (чтение - запись) и сохраняют исходные данные в моментальном снимке всего тома. Снимок тома может предоставляться в виде доступной только для чтения копии. Открытый только для чтения том моментального снимка поддерживается в качестве одного из вариантов конфигурации базы данных SQL Server SSD. Такой подход может стать мощным инструментом для работы с оперативной информацией в промышленной среде, когда необходимо только чтение, но оно должно выполняться с нескольких серверов.
Некоторые подсистемы, вместо подхода с созданием моментального снимка тома и предоставления его только для чтения, используют механизмы распределенных блокировок. Это позволяет нескольким серверам работать с одним томом и для чтения, и для записи. Среда с распределенными блокировками может быть весьма производительной, но она не отвечает требованиям по обеспечению единого образа данных на указанный момент времени (point-in-time) для вторичных серверов. Оперативные данные предоставляются всем серверам, подключившим себе такой том. SQL Server SSD не поддерживает запись в файлы базы данных. Буферный пул SQL Server нельзя синхронизировать по заданным моментам времени с вторичными серверами, поскольку это породило бы разного рода проблемы, связанные с грязным чтением.


Последовательность создания и использования SQL Server SSD

Упреждающее заполнение локального кэша

SQL Server 2005 Enterprise Edition старается побыстрее заполнить локальный кэш данных сервера, причём, ещё на стадии инициализации. Рост используемой памяти во время инициализации принято называть ramp-up стадией. На этой стадии, всякий раз, когда для исполнения запроса требуется прочитать одну страницу, будет на самом деле прочитано восемь страниц (64 КБ), которые будут распределены в буферном пуле. Упреждающее заполнение кэша помогает уже после рестарта сервера сделать время ожидания чтения одиночных страницы заметно меньше, чем в предыдущих версиях. Это дает возможность быстрее заполнить кэш данных, после чего SQL Server будет выдавать больше страниц из кэша, чем с диска (возрастёт процент логических чтений).
Не поддерживающие серверную архитектуру NUMA компьютеры, обычно, реализуются в виде одного узла. Когда у сервера памяти много, это может стать существенным преимуществом, потому что в таком случае возможна более быстрая перезаливка кэша данных. Каждый узел закреплён и управляется собственным монитором ресурсов. SQL Server имеет информацию о всех процессорных узлах и требующихся для них объемах и конфигурациях памяти, которая находятся в домене близости каждого узла. Это относится и к буферному пулу. Перечисленные возможности позволяют считать SQL Server полностью NUMA-совместимым сервером.

Шифрация файловой системы (EFS)

Файлы баз данных SQL Server могут храниться в зашифрованном NTFS виде. Однако, эту возможность следует использовать с осторожностью, поскольку при этом будут отключены возможности асинхронного ввода-вывода, что может вызвать проблемы производительности. Если ввод-вывод осуществляется с зашифрованным файлом, планировщик SQL Server прерывает свою деятельность пока этот запрос ввода-вывода не буде завершен. Такие операции SQL Server, как упреждающее чтение, тоже будут отключены для зашифрованных EFS файлов. Мы рекомендуем по возможности использовать встроенные в SQL Server 2005 криптографические возможности вместо шифрации средствами EFS.
Использование EFS для SQL Server целесообразно использовать для физической защиты файлов баз данных. Используйте эти возможности на портативных ЭВМ или в тех инсталляциях, где необходима физическая защита данных.
Если сервер должен быть развернут в среде с EFS шифрацией, желательно выполнить следующие рекомендации:

  • Используйте выделенный экземпляр SQL Server.

  • Протестируйте производительность приложения и СУБД в такой конфигурации.

  • Протестируйте работу сервера с привязкой ввода-вывода и без такой привязки. Использование привязки ввода-вывода может обеспечить работу SQL Server в псевдо -асинхронном режиме.

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

[В начало]

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

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

64-разрядные установочные пакеты, выделение памяти кластера и другое
Nancy Michell
Эффективность работы электронной документации: В. Каждый раз, как я открываю электронную документацию (BOL) по SQL Server™ на своей настольной системе, она работает очень медленно, хотя у меня и быстрый компьютер. На других системах, которыми я пользуюсь, этого не происходит. Что может быть причиной возникновения задержек?

Построение индексов - часть 7: Фоновое построение индексов (Часть 1)
Lyudmila Fokina
В SQL Server 2005 стало возможно строить, перестраивать и удалять* индексы фоновом режиме. Это позволяет проводить текущее обслуживание, не прерывая основной работы. Опция "ONLINE" делает возможным параллельный доступ к данным таблицы/индекса во время построения/перестроения индекса. Например, когда один пользователь перестраивает кластерный индекс, другой пользователь может получать и обновлять данные таблицы, для которой перестраивается индекс. Для сравнения: при автономном построении индекса, построитель индекса держит эксклюзивную блокировку на таблицу и индекс в течение всего времени построения, что не дает получать или обновлять данные таблицы/индекса.

Мониторинг SQL Server
Кевин Клайн
Какой вопрос меньше всего хотелось бы получить администратору баз данных? Вероятно, сообщение от пользователя об ухудшении работы приложения или вопрос о том, что случилось с базой данных. Приходится откладывать все дела и переходить в "аварийный режим", гадая, надолго ли это. Так как одной из основных обязанностей администратора баз данных является обеспечение качественного функционирования промышленных баз данных, остается только максимально быстро устранить неисправность. Времени на выяснение причины сбоя, как правило, нет.

Криптография в SQL Server 2005. Кому это нужно?
Laurentiu Cristofor
Для тех, кто читал мои предыдущие посты, вопрос в заголовке может показаться удивительным. Я хочу вас сразу успокоить: эта статья не про то, что криптография это бесполезная технология. Она про то, что криптография может помочь в решении не всех задач и уж точно не является каким-то общим универсальным решением любых проблем. Информация, которая содержится в статье, не привязана к какому-то конкретному продукту. Я хочу обсудить не то, как криптография реализована, например в SQL сервере, а вопросы применения криптографии в целом. Ограничения, о которых я хочу рассказать - это ограничения стандартных криптографических методов, а не ограничения их реализации в каком-то конкретном продукте.

SET DATE_CORRELATION_OPTIMIZATION ON?!
Александр Гладченко
Случайно наткнулся ещё на одно, по-моему, незаслуженно обойдённое всеобщим вниманием новшество, которое в моих базах данных (которые просто напичканы datetime) может принести очень даже заметный выигрыш в производительности. Речь идёт о параметре уровня базы данных: DATE_CORRELATION_OPTIMIZATION, который, как и следует из его названия, может использоваться для включения хранения коррелированной статистики для полей типа datetime, и при построении планов исполнения запроса, информация о корреляции дат пары таблиц будет использоваться, как дополнительное, сокращающее объём выборок ограничение. Подробное описание того, какие условия необходимы для сбора статистики по корреляции дат, как это настроить и как заставить работать на благо производительности, Вы можете прочитать в этой статье BOL: Оптимизация запросов, которые обращаются к коррелируемым столбцам типа datetime

Доступен пакет дополнений для Microsoft SQL Server 2005 - февраль 2007 г.
Александр Гладченко
25 мая 2007 года на сайте загрузки Microsoft стал доступен пакет дополнительных возможностей для SQL Server 2005. Этот пакет подготовлен в дополнение к февральскому обновлению Microsoft SQL Server 2005, и представляет собой набор независимых пакетов установки, обеспечивающих дополнительную функциональность при работе с SQL Server.

[В начало]

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

Battery backup write cache for controllers - should it be disabled?
Satya SK Jayanty
You might be aware that now a days many high-end and high-spec disk subsystems provide high-speed cache facilities to reduce the latency of read and write operations. In general this cache is supported by a battery-power backup facility, which is necessary to maintain the data in cache in case of any power surge in hardware. But the implementation of this method varies from manufacturer to manufacturer. Microsoft recommends to switch off (disable) this feature for write cache, for 100% reads method this cache can be enabled and in any case you must test the implications.

Committed and Updates
Craig Freedman
Let's try an experiment. Begin by creating the following simple schema:

Geek City: Accessing auxiliary tables during Data Modification
Kalen Delaney
After reading Hugo's post about when snapshot isolation doesn't really live up to its promise, I decided to do some research of my own. I met with Stefano Stefani, one of the engineers at Microsoft on the storage engine team and we talked about what kinds of locks are taken during the data modification operations. We know that when a row is actually updated, SQL Server will take an Exclusive (X) lock, but there's a lot more to the story than that.

At the End of Paths Taken (Modifying Where Clause when All Parameter Values are Selected)
Brian Welcker
With the return of "Select All" in Service Pack 2, all multi-value parameters allow the user to pick all of the available values. But what if you want to remove the predicate from your WHERE clause instead of generating a long set of values in the IN predicate?

SQL SERVER - 2005 Comparison EXCEPT operator vs. NOT IN
Pinal Dave
The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there is no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.

Using SQL Profiler for Analysis Services 2005
Baya Pavliashvili
Those of us who have used SQL Server Profiler acknowledge it as one of the best tools for troubleshooting and tuning relational database applications. If you need to find what indeed is happening "under the hood" Profiler is by far the best place to start. If you want similar functionality with OLAP Services (part of SQL Server 7) or Analysis Services 2000 you're simply out of luck. Fortunately we're blessed with the ability to use Profiler with Microsoft Analysis Services (MSAS) 2005. In this tip, I'll introduce you to usage of SQL Profiler to monitor, troubleshoot and optimize your analytical applications.

Transform your SQL, C# and VB code with the Code Prettifier
Phil Factor
The Simple-Talk SQL Prettifier was first launched in July 06 in order to provide a means to prepare T-SQL code for HTML-based publication. I was driven to write it by my frustration at having to paste dull monochrome, badly-formatted SQL code into blogs and forums.

Writing Faster T-SQL
Jacob Sebastian
How many times have you wished that your queries performed better? Performance Tuning is a bit of an art, but learning about new techniques and which things work help grow your knowledge. Jacob Sebastian brings us the first part of a series on writing better performing queries.

Microsoft DPM
Jay Dave
This is a product that we have not heard much about, but Microsoft has a Data Protection Manager, designed to help ensure you can recover your databases by centralizing your backup files. Author Jay Dave brings us a short overview on this product.

Select anything from any field in a proc
Michael Berry
We all know the user .... the one who wants it all. well... one time I met one that wanted a crystal report that would prompt them for 12 different parms. She wanted to be able to narrow the amount of records that came back just by filling more fields. Heck, she didn't even want to fill out the whole value, but maybe just part of whichever ones she wanted to try. Sounds like a lot of "IF' statements and wildcards eh?

Next Version of SQL Server Slated for 2008
Keith Ward
Microsoft kicked off its first-ever Business Intelligence (BI) Conference in Seattle with a keynote speech by Business Division President Jeff Raikes, who spoke in general terms about Microsoft's efforts to integrate existing products into businesses processes, without offering much in the way of specifics. He also announced the upcoming availability of PerformancePoint Server 2007 and the acquisition of SoftArtisans.

Real-World Data Mining
John Charles Olamendy Turruellas
Build a real-world data mining app that enables you to examine historical customer data and make predictions about new customers by comparing the two.

Microsoft Moves DBMS into the VS Developer Mainstream
Lee Th?
Microsoft Moves DBMS Into the Mainstream At VSLive! SF, Britt Johnston demonstrated how Orcas will incorporate exciting new tools for database management, while maintaining continuity and connectivity with legacy DBMS. "Orcas" will include exciting new tools and functionality for database management within VB and C# while maintaining continuity and connectivity with legacy DBMS.

Match by Exception
Joel Champagne
Create more flexible, data-driven solutions with the aid of a match by exception design pattern.

SQL Slip Up
Jim Desmond
What happens when a query goes wrong -- very, very wrong. One of our largest customers was using our software to support its training department. The software let thousands of employees register for classes, checkout resources and see their progress toward certification. They needed some customizations done to support a government client, so as the only person with experience in Microsoft SQL Server, I traveled to the customer site. Our company president, who also doubled as our head programmer, came to support the database side.

Microsoft OLE DB Provider for Visual FoxPro 9.0
Microsoft
The Visual FoxPro OLE DB Provider (VfpOleDB.dll) exposes OLE DB interfaces that you can use to access Visual FoxPro databases and tables from other programming languages and applications.

SQL Server 2005 Paging Results
Frank Kerrigan
With SQL Server 2005 it is now a lot easier to use paged queries than in previous versions. I will be using NorthWind db (mostly) so you can also use the examples I have provided. I will keep the examples simple; anything complex will only cause confusion. I will start with "traditional" methods such as SELECT, TOP, and then move onto the specific SQL Server 2005 paging examples.

Stored procedures are bad, m'kay?
Frans Bouma
Let me start with a blunt statement: stored procedures are bad, they are a bad way to formulate data-access logic. I can't state that enough. Today I stumbled into a blog by Rob Howard, which tries to convince the reader that whatever you do, use stored procedures! With tears in my eyes I've read the arguments he brings to the table. Some are silly and one is even based on completely wrong information and assumptions and so far away from the truth it hurts. I've blogged about stored procedures before (here, here and here) and I used them a lot for 8 years, but I'm now almost stored procedure 'free' for 8 months now, and I love the feeling. The reason is obvious: the stress of maintaining a lot of stored procedures, to write another stored procedure for each thing you want to do, is gone. Dynamic SQL is the future. (Dynamic SQL is generated on the fly by a generic piece of code which gets various data as input and generates a parametrized query from it. Which can be cached on the client and will be cached on the server. Dynamic SQL generated based on objects written in C# or VB.NET).

Roundtrips and the real bottlenecks
Frans Bouma
Now the discussion of yesterday about Dynamic SQL and Stored Procedures are slowing down, I'd like to mention a little thing that popped up during the debate: roundtrips.

Yay! A new Stored Proc vs. Dyn. Sql battle!
FransBouma
You'd think that by now everyone would have said everything that you can say about Stored Procedures vs. Dynamic SQL, but apparently Eric Wise and Jeremy Miller disagree with that and have started another iteration of this Never Ending StoryTM.

Essay: The Database Model is the Domain Model
FransBouma
To work with data on a semantic basis, it's often useful to specify general definitions of the elements a given portion of logic will work with. For example, an order system works with, among other elements, Order elements. To be able to define how this logic works, a definition of the concept Order is practical: We will be able to describe the functionality of the system by specifying actions on Order elements and supply with that a definition of that element Order.

ADO.NET Entity Framework cut from .NET 3.5/Orcas
FransBouma
First, read the announcement here. Had that deja-vu yet? Me too . But let's not get into that right now, because I do think the Entity Framework situation is different from the WinFS/ObjectSpaces situation. So what's going on here?

API's and production code shouldn't be designed by scientists
FransBouma
One of the biggest mistakes Microsoft made in Database land was the absense of a proper paging mechanism in SqlServer 7 and SqlServer 2000. (No, don't come to me with tricks with @@ROWCOUNT because these don't always work in all situations). People had to use temp tables to get a mechanism which always works in all situations. What a surprise it was to see that Microsoft said they solved it in SqlServer 2005: they added a construct which offered paging inside the database without the necessity of temp tables. Though, who designed this feature? Did that person ever write production SQL code or did that person ever write normal business applications? Did anyone ever tell that person that all other major databases on the planet, except Access, had a simple paging mechanism build in so anything more complex than that would be bad? Apparently not.

I need your help or how to make multiselect work seamlessly in MDX
Mosha Pasumansky
This post is unusual for me. In this post I am not going to give information to my readers. I am going to ask my readers to give information to me. For quite a some time now I have been looking into problem of interaction between MDX calculations and multiselect. Multiselect is a feature of pretty much every single OLAP client tool, where the user decides to slice not by single hierarchy member, but by several members. The way different client tool generate MDX for the multiselect differs (query calculated member with Aggregate, sets in WHERE clause, subselects etc) and each method has advantages and drawbacks, but this post isn't about how to write MDX SELECT statement to express multiselect in the best way. It is about the fact that each one of these methods breaks in one form or another when the query touches a cell using huge class of MDX calculation, specifically the ones which reference implicitly or explicitly CurrentMember property of the hierarchy by which multiselect is done. Sometimes it is possible to do something about it, and I even have written couple of blogs on the subject: "Writing multiselect friendly calculations" and "Multiselect friendly MDX for calculations looking at current coordinate". However, the methods I describe in these posts are cumbersome, not universal (i.e. don't solve all the scenarios) and hurt performance even when multiselect is used. Additionally, these methods don't address scenarios which are not pure multiselect, but in related functionality, such as Visual Totals and Custom Grouping. Lastly, most of these methods are useless when the queries are generated by Excel 2007 - which is a pity, because Excel 2007 compared to the previous versions of Excel is hands down the best OLAP browser, and according to OLAP Survey, Excel is the most popular client tool for Analysis Services.

Identify and troubleshoot slow running queries in SQL Server
Satya SK Jayanty
Achieving the performance for slow running queries is not a rocket science to understand. The database performance is dependant on implementing optimum database design with sensible hardware-based configuration and capacity planning. So how you can identify where the performance is degraded, say when you desing it is easy to identify the large tables in the database and the more complex processes that the database will perform. In case of a normal database that has been outgrown since last few years having difficulty in achieving the performance, then take help of this essential checklist of PerformanceAudit to understand the behaviour of queries and other processes in the SQL Server.

Using the OUTPUT and OUTPUT INTO clauses in SQL Server 2005
Decipherinfosys
SQL Server 2005 introduced the OUTPUT & OUTPUT INTO clauses. Prior to SQL 2005, one had to use 2 SQL statements in a batch to get the values that were just inserted/updated/deleted by the DML statement. These can now be done using a single statement with the help of these clauses. These can be very useful for logging/auditing purposes, for confirmation messages or general application processing.

Real Programmers don't need to write test applications!
Jeff Smith
You are a very important, talented, enterprise-level programmer! You write and maintain millions of lines of code, compiling your applications takes several hours, and your databases contain hundreds of tables with millions of rows. Even the best and brightest and most experienced programmers, now and then, come upon a class or library or technique that is new to them. That's right, even you! You can admit it, no one's looking. But it really has no effect on your productivity, because you can quickly refer to the documentation or books or Google or many other resources to obtain the knowledge you need without missing a beat. Heck, you don't even need to post a question in a programming forum -- who could help you, after all? It's usually the other way around, right?

Now it works - Now it doesn't; data conversion timebombs
Tony Rogerson
Data type conversions can often present problems in a database, specifically when converting from character to numerical data. I'm not talking about precision or rounding problems, not this post anyway, I'm talking about situations where your queries work fine and then one day without change to your query it just stops working with an error like this one...

EMF Rendering and Persisted Streams
John Gallardo
EMF rendering in Reporting Services has an interesting history. When SSRS first shipped, we did not support "direct printing" from within the web interface. Customers revolted. They wanted a "Print" button on the toolbar which would provide a full featured print experience. Many other web applications don't actually do this, choosing instead to present the user with a "printable" HTML page or some other format such as PDF which they then print within their PDF reader. Rather than this workaround though, it was decided that we would implement an EMF renderer and provide a client-side ActiveX control which would send the EMF to the print spooler.

SqlCredit - Part 5: Adding Card, Vendor, and Purchase, Plus Much Refactoring
Rob Garrison
I am writing this month from Redmond, where I am attending the SQL Server Katmai "System Design Review". Considering that we're fairly close to seeing the first publicly-available CTP for Katmai, I have decided to end my struggle with supporting both SQL Server 2000 and SQL Server 2005 in SqlCredit. As of this month, this project supports only SQL Server 2005. Those of you that have already made that plunge with your products are probably thinking "finally". Those of you that are still required to support SQL Server 2000 in your jobs are probably wishing it were that easy for you to drop support.

Recover from a corrupted transaction log file - on test server only
Satya SK Jayanty
Say one fine morning you had a call stating one of the databases have lost the transaction log file due to a hardwrare issue (for discussion sake). Then the recovery option is to restore from the latest backup, that is a best practice too. In case if you don't have the recent backup and still you have to recover the database by rebuilding the transaction log file, then SP_ATTACH_DB_SINGLE_FILE_DB will come into picture. This may not be successfull as the BOL reference:

FAQ: SQL Server 2005 features and functions
Adam Machanic
Not surprisingly, SQL Server discussion boards are continually brimming with questions about new features and functions in SQL Server 2005. MVP Adam Machanic recently provided us with his top 10 beta questions on the subject. Now he answers popular questions that have cropped up since the launch of SQL Server 2005. Use this set of FAQs to help prepare yourself for working with the new DBMS.

FAQ: Top 5 SQL Server backup and recovery problems
Greg Robidoux
Backup and recovery in SQL Server is another one of those 'do or die' areas. We've tallied your page hits and compiled a Top 5 list of frequently asked questions on the topic, so you'd have a quick reference. Find out how to reduce the size of a backup file in SQL Server 2000, how to utilize a transaction log to recover lost data and more.

User Defined Data Types in SQL Server 2005
Dinesh Priyankara
One other use of the new CLR integration in SQL Server 2005 is the ability to create user defined data types using managed code. Author Dinesh Priyankara brings us a first look at this capability with some code you can use.

Secure Access to Report Data
Jan De Clercq
It's simpler than you think to use SSRS's roles and map them to AD users and groups. In the healthcare and financial industries, where database applications often process confidential information, using authentication and encryption to secure access to data has become a common practice. Indeed, for companies that are affected by industry regulations such as the Health Insurance Portability and Accountability Act (HIPAA), securing data is an absolute requirement. For many other industries, however, ensuring the security of reporting systems and the databases they access remains a low priority on system designers' and administrators' to-do lists because of its seeming complexity. It doesn't have to be difficult, though, especially if you're using SQL Server Reporting Services (SSRS) as your reporting system. Bundled with SQL Server 2005, SSRS can simplify securing your reporting data by using a role-based access control (RBAC) model to leverage your existing Windows Server 2003 or Windows 2000 Active Directory (AD) infrastructure. Let's look at how you can configure SSRS to secure access to your data. . . .

Microsoft Trumps Oracle's Record-Breaking TPC Score
windowsitpro.com
Last week, Microsoft announced its latest TPC-H benchmark score, which was quite impressive. Microsoft's latest score--posted on May 21, 2007--was 60,359 queries per hour (QphH) @3t with a Price/QphH of $32.60 compared with Oracle's best score--posted on May 14, 2007--of 37,813 QphH @3t with a Price/QphH of $38.00. Almost double the performance for a lower cost per query? Sign me up!

Totals
Joe Celko
SQL Apprentice Question. I have a table that is populated everyday with daily totals, I'm trying to teach myself SQL using this table. Someone kindly gave me a query that gave the weekly totals based on the table:

SQL Query Help
Joe Celko
SQL Apprentice Question: Need to learn SQL queries again (SQL 2005). Need help with the following situation:

Counting Days in MDX
Mosha Pasumansky
Time dimension is special in OLAP. Many MDX functions usually only make sense when applied to Time dimension (PrevMember, Lag, ParallelPeriod, PeriodsToDate, ClosingPeriod etc); semiadditive measures work differently with Time etc. Today, however, we will talk about much simpler subject - counting number of days in the currently selected period. There are all kinds of uses for this metric, for example computing averages over time (this is usually interesting in inventory applications to get average level of inventory). For this article we will use Adventure Works cube and compute average of [Internet Sales Amount] over [Ship Date] dimension. We could say that really for computing averages over time, one should use AverageOfChildren semiadditive measure. This is true with two caveats. First, semantics of AverageOfChildren semiadditive aggregation with respect to treating NULLs is the same as with Avg function - i.e. it will not count days which had no sales, and if we wanted to count such days, then AverageOfChildren won't work. Secondly AverageOfChildren is available only in Enterprise Edition. Lastly knowing number of days (or other time periods) in the currently selected time is useful in other calculations as well, we use average because it is simple enough, yet illustrative for our goals.

Vardecimal Storage Format in SP2
Decipherinfosys
In case you haven't gone through the list of all the new features included in the second service pack of SQL 2005, here is the link. These are also included in BOL if you have the latest one installed. We had covered one of the new features logon triggers in one of our blog posts before. Another interesting new addition is the new storage format - vardecimal. Oracle has had variable decimal storage instead of the fixed one since a long time - Oracle Guru Tom Kyte had answered one of the questions on his site regarding this - you can read more on that here.

Where do I create my KPIs?
Nick Barclay
In recent times there have been requests by many (including me a few months ago) to get more clarification on where KPIs are best defined - SharePoint 2007, SSAS cubes or BSM/PPS. So which one should we use? The consultant's answer: It depends. All three alternatives are excellent when used within their boundaries and expectations are set for what each can and cannot do out-of-the-box.

Restoring and Attaching is a one way street
Simon Sabin
Many people often ask how they can us a database in SQL 2000 that they have attached or restored to SQL2005. Unfortunately this is a one way street, once upgraded the only way of using a database in a previous version is to copy the schema and data manually. You can't restore or attach a database even if the compatibility has been kept at the previous version.

ASP.NET DropDownList and Unexpected Data
Matt Mostly
You are data binding to a table. One of the columns, say US state, should have a limited domain, but for various reasons, values outside of that range appear. For example, you might be importing the data from a second system that uses the state field for country and state.

Demo VII: Tombstone Cleanup and Detecting Stale Offline Clients
Rafik Robeal
Ready for a new offline application demo? This one is really cool. In this demo I tried to show off one important feature of the framework design and how to use it to elegantly solve one common issue with synchronization systems. So let's first talk about the problem we trying to solve.

A Couple of Report Builder - UDM Hacks
Teo Lachev
If you target the SSRS Report Builder as an UDM client you may find the following two workarounds (aka hacks) useful. Both hacks require manually updating the SMDL definition. Alas, so much about model auto-generation 9.

SQL Server 2005 Integration Services - Part 49 - Maintenance Plan Tasks
Marcin Policht
In the most recent articles of our series covering SQL Server 2005 Integration Services, we have focused on maintenance plans activities, represented by a number of tasks in Maintenance Plan designer of SQL Server Management Studio (including additional caveats introduced by Service Pack 2 installation). As we have demonstrated, an identical set of tasks is also available in the Control Flow area of SSIS Designer in Business Intelligence Development Studio. Despite apparent similarities in the way these tasks are presented in both interfaces, it is important to note that there are some fairly significant differences between them (which reflect not only their unique internal characteristics, but extend also to such mechanisms as maintenance plan storage, scheduling, and execution). Today, we will review these differences, by examining compatibility and level of integration between these two alternative approaches to automating routine administrative responsibilities.

SQL SERVER - SQL Joke, SQL Humor, SQL Laugh - SP
Pinal Dave
One of my Friend send me(in email) following stored procedure. I laughed when I read it. Please enjoy it. It is here for amusement purpose only. Never use on development or production server. This is already dangerous you have been warned.

Improve query performance with SQL Server 2005 execution plans
Baya Pavliashvili
One of the most exciting and useful additions to the query performance tuner's arsenal is its ability to force the database engine to use a pre-generated query execution plan. Previous versions of SQL Server allowed a DBA to specify query hints, to perhaps alter the transaction isolation level or to enforce usage of a given index or a join type. With SQL Server 2005, you can actually create a full execution plan and feed it to a query. You can also inject query hints into an application without modifying the code.

SQL Server 2005 DDL Trigger Workbench
Robyn Page and Phil Factor
How about automatically tracking and logging all database changes, including changes to tables, views, routines, queues and so on? With SQL Server 2005 it isn't that hard, and we'll show how it is done. If you haven't got SQL Server 2005, then get SQL Server Express for free. It works on that! While we're about it, we'll show you how to track all additions, changes and deletions of Logins and Database Users, using a similar technique.

The T-SQL Quiz
Grant Fritchey
Longtime SQL Server DBA and author Grant Fritchey decided to quiz his developers on how to perform some simple functions in T-SQL. Read about his results and see how you might do in taking his quiz.

Best Practices for Date/Time Calculations in SQL Server
Alex Kozak
A few months ago, I received a message from quite an experienced DBA and database programmer asking me whether I knew a nice trick for manipulating date/time data without using Microsoft SQL Server's date/time functions-something like GETDATE() + 10. It reminded me of a few internet forum discussions about the same subject in which participants were irked by the fact that Microsoft missed such a convenient feature. I decided that the topic deserved a deeper discussion. This article is the result.

Not In v Not Equal
Ken Johnson
Is it better to use NOT IN() or > in a T-SQL query? Ken Johnson had the question put to him and decided to investigate them both. Read about how these two functions perform.

Upload multiple XML files to an XML data type column
Muthusamy Anantha Kumar
SQL Server Database administrators often have requirements such as importing multiple files to a table on a database. This article discusses how to upload multiple files, especially XML files, to the SQL Server database XML data type column.

SQL Server 2005 Deployment Guidance for Web Hosting Environments
Alex DeNeui
SQL Server 2005 is an ideal database platform for use in shared and dedicated Web hosting environments. This paper provides best practices for configuring SQL Server 2005 to optimize security, tenant isolation, and the performance of your hosted SQL Server 2005 deployment. Sample scripts for provisioning users and databases for use in shared hosting are included.

Using advanced table hints in SQL Server
Tim Chapman
Tim Chapman explores the benefits and side effects of using three types of table hints when running queries in SQL Server: READCOMMITTED, REPEATABLEREAD, and SERIALIZABLE.

[В начало]

ФОРУМ SQL.RU

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

Microsoft SQL Server. Полезные алгоритмы от SQL.RU (+CD)
HB for Glory !
-=Сложный запрос =- (LIFO)
срочно необходима консультация специалиста по SQL
Оптимизация UPDATE
MSSQL XML Internet
MS SQL 2000 в internet
Оценка читаемости процедур
Возвросла нагрузка на процессор в 1.5 раза при переходе на MS SQL 2005 c 2000
Оптимизировать запрос с table-переменными
Поймал сейчас офгенный глюк
часовой пояс
Я не понимаю как сделать trigger :(
Слияние полей из разных строк запроса
кто ни будь понимает, чеза хрень творится с сп2?
Как работать с MSSQL 2005?
есть трейс, с чего начать оптимизацию.
странности с truncate table
синхронизация баз
помгите пожалуйте!мне нужно срочно это сделать.!заранее спасибо

[В начало]

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

SSIS - OLE DB Command, как получить ERROR OUTPUT?
Вопрос по репликации SQL 2005
Совет спрошу по плану +
работа JOB - какая учётная запись?
SQL запросы на добавление, обновление и подсчёт.
Уменьшение файла данных
Оптимизация кода
проблема с запуском xp_cmdshell

[В начало]

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