Group items tagged

"This page provides a comparison of key capabilities between SQL Server 2008 Enterprise, Standard, Workgroup, Web and Express editions."

"Column-Based Query Accelerator will help dramatically increase query performance ~10x and reduce performance tuning through interactive experiences with data for near instant response times and streamlined setup which removes the need to build summary aggregates."

"Every couple of weeks on one of the forums someone will ask a question about how to restore a backup from SQL 2008 to SQL 2005. The answer to the question is always, you can't restore a backup to a lower version of SQL Server. If you need to migrate backwards it is an entirely manual process. I learned this unfortunate truth a few years ago after upgrading a development server to SQL Server 2005 ahead of upgrading the production server for testing by the developers of our eCommerce application. At the time there was a new database for another web project in development that didn't exist in production, which was on SQL 2000 still, and when I had to move it to QA, also on SQL 2000 still, what was originally planned as a backup/restore turned into a "What do I do now?" moment."

"Like many developers, I happen to be a fan of using uniqueidentifier columns as primary keys on database tables (as opposed to the other common practice of using integers). While I don't do this all the time, in many cases I think it just makes sense, especially when the records in and of themselves don't share a "sequential" relationship to one another. I understand this is a rather controversial topic in some circles; with good arguments on both sides. Quite frankly, whether you personally agree with this practice or not is beside the point. For the purposes of this discussion, it's just important that you understand two things: one, that this done all the time; and two, that this scenario was not properly supported under Entity Framework V1."

" SQL Sentry Plan Explorer is a FREE tool that builds upon the graphical plan view in SQL Server Management Studio (SSMS) to make query plan analysis more efficient. It is a lightweight standalone app that contains many of the plan analysis features introduced in SQL Sentry v6. It does not require a collector service or database. "

"Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view."

"Database development is many aspects behind in comparison to regular application development. If you look how database development is integrated with regular application development or development processes it becomes even more obvious that there is room for improvement in this area. When asked, you will find that many database developers are spending their valuable time writing, maintaining, and testing database deployment scripts. The practices may vary, some write them by hand, some use schema comparison tools to generate the script and modify that, and some went all the way to create their own tools to generate these kinds of deployment scripts."

"DBAs and developers alike find it hard to effectively develop, deploy and manage a data-tier application throughout its lifecycle. SQL Server developers today want to create a data-tier application they can test, declaratively deploy across multiple instances while the system deals with version upgrades. SQL Server DBAs on the other hand want to know deployment requirements a priori and they too want the system to perform some of the mechanical upgrade operations instead of managing data-tier applications via a bunch of scripts. In this video we will dive into Microsoft's investments in the SQL Server data-tier application space and demonstrate the concepts and tools that simplify development and deployment of data-tier applications using Visual Studio 2010 and SQL Server Management Studio in SQL Server 2008 R2."

"Resource Governor is a new feature and one of the most anticipated management features in SQL Server 2008. It bolsters SQL Server performance by allowing DBAs to establish resource limits and priorities on processor and memory-related workloads and resource pools. By defining resource limits on resource consumption, it is possible to prioritize multiple workloads in order to ensure consistent performance and maximize the experience of the end users and the efficiency of the applications without degradation."

" This is a blog post I've been meaning to do for a while, and I've recently noticed some info on the web about checkpoints which is a little misleading, so I want to do a quick post to explain how checkpoints work as far as log records are concerned. "

"I guess this could be considered a continuation of my last post about SQL Server Memory, Understanding the VAS Reservation (aka MemToLeave) in SQL Server, but for a long time I have noticed that when people don't understand how SQL Server uses memory on their server, they immediately begin to think that SQL Server has a memory leak. So in this post I'll dive into how SQL Server allocates and uses the physical memory available on a server."

"Many experienced database professionals have acquired a somewhat jaded view of parallel query execution. Sometimes, this is a consequence of bad experiences with older versions of SQL Server. Just as frequently, however, this view is the result of misconceptions, or an otherwise incomplete mastery of the techniques required to effectively design and tune queries for parallel execution."

"Lurking on any machine that has Windows Scripting Host installed (virtually all machines these days, although scripting can be disabled), is a powerful Regular Expressions facility, the VBScript.RegExp scripting object. You can get to it from any COM client that supports the IDispatch interface. IDispatch, you'll recall, is COM's popular late-binding interface - it allows applications to use COM components without knowing anything about them at compile-time. In T-SQL, we get to IDispatch via the sp_OA stored procedures. Via a simple UDF, we can access the RegExp object as though it were part of T-SQL:"

"In SQL Server 2008 Microsoft has introduced a number of new date and time data types. One of these is the datetimeoffset data type. This data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved in UTC or a particular timezone based on this offset. There are also new functions to allow for conversions between different time zones using the new function SWITCHOFFSET(). "

"The Crimson Consulting Group has just published a white paper worth reading: Comparing the Business Value of Microsoft SQL Server 2008 R2 and Oracle Database 11g, Perceptions vs. reality in the RDBMS marketplace (April, 2011). The traditional user-friendliness of a mid-tier product has matured in terms of scalability. Today, you will find SQL Server 2008R2 installations running on 256 core machines -- scalability is no longer an issue. Unfortunately, the traditional scalability product has not grown significantly friendlier to developers and users; this leaves SQL Server 2008R2 the clear value winner for both lowest development expense and total cost of ownership for operations."

Free "SQL Server Statistics" eBook and a trial of SQL Monitor

"SQL Server 2008 introduces two tracking features that enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Before these features were available, custom tracking mechanisms had to be implemented in applications. These custom mechanisms often required schema changes to the tracked table or the use of triggers. Neither change data capture nor change tracking requires any schema changes at the source or the use of triggers."

Dapper - a simple object mapper for .NetOfficial Github clone: https://github.com/SamSaffron/dapper-dot-net FeaturesDapper is a single file you can drop in to your project that will extend your IDbConnection interface. It provides 3 helpers: Execute a query and map the results to a strongly typed Listpublic static IEnumerable Query(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

"A simple user interface to automate the batch processing of comparing Sql Server table data using the tablediff.exe utility that comes with Sql Server 2005/2008. "

"The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:"