Articles filed in category 'SQL Server'

Kevin learns the hard way how to estimate a process’ duration and he uses a clever bit of code to figure it out.

ADO.NET has a strong and powerful disconnected model.It allows programmers to build a web of in-memory objects and relate contents coming from different tables and even from different data sources. When inter-related tables are involved with the process of query and update, code strategies are important to preserve scalability and maintain high performance. Sometimes compound queries can be more effectively accomplished splitting queries; sometimes not. Submitting change...s in batch mode, exploiting the DataSet and its disconnected model, often appears as the perfect solution. However, what if you need to move dozens of MB? The serialization mechanism of the DataSet would increase that by a factor. Tradeoffs is the magic word.See More

Nov/Dec 08 Doc Detective column

Mike explores Azure SQL Database and what it has the potential to do for large and small businesses.

If you want to be a great SQL Server developer, you’ll want to pay attention to these 13 key points. Kevin Goff covers both best practices and common pitfalls of Business Intelligence.

Helen shows you the ins and outs of creating really useful charts and graphs with Tableau. You’ll never make a boring old pie chart again.

Kevin shows us how to solve tangles in SQL Server, and in this article, he looks at some questions that have come up since his last article on ETL practices in Data Warehousing.

In this article, you will create a CODE Framework RESTful service and an iPhone application from scratch. For the client side, you will utilize XCode (yes, you’ll need a Mac!), which uses Objective-C as the primary language. This article won’t teach you the language; you need to know the basics of Objective-C. Even if you don’t know anything about it but want to code right away, read the article “Building a Twitter Search Client on iOS,” by Ben Scherman, available for al...l CODE readers in the Xiine application for Windows, Android, and iPhone. For the database, you’ll use any instance of SQL Server 2008. The service will be written in C#, using the latest version of CODE Framework, available on http://codeframework.codeplex.com, where you will find not only the download link, but also a lot of useful information.See More

Creating basic data access stored procedures is time consuming and boring work. Relieve the tedium by writing code that writes these stored procedures for you.

With the functionality provided by the .NET runtime, building data access components with C# (pronounced "c-sharp") is not difficult.In this article, we discuss how to access data stored in a SQL Server database. We will then review the steps necessary to build a Dynamic Link Library and an Executable file in C# to query a table and display the results.

Distributed applications are now prolific in the enterprise and more and more users are relying on network connectivity both on site and on the go so they can remain productive anywhere and at any time. Since network connectivity cannot always be guaranteed, what happens when the network goes down or a network connection is simply unavailable? How can you provide your users with the best connected experience regardless of the state of the network?

C# 3.0 includes a few syntactical additions to the language. For the most part, Microsoft added these language additions to support Language Integrated Query (LINQ). These features include (but are not limited to) lambda expressions, extensions methods, anonymous types, implicitly typed local variables, automatic properties, and object initializers.

With Sync Services for ADO.NET, developers can easily optimize their online experience by caching data locally within the easy-to-deploy SQL Server Compact embedded database engine.In this article I’ll cover how Sync Services for ADO.NET was designed to fit the growing developer needs for caching data locally in online-optimized, offline-enabled applications.

SQL Server2005 Distilled Need to get your arms around Microsoft SQL Server 2005 fast, without getting buried in the details? Need to make fundamental decisions about deploying, using, or administering Microsoft’s latest enterprise database? Need to understand what’s new in SQL Server 2005, and how it fits with your existing IT and business infrastructure? SQL Server 2005 Distilled delivers the answers you need–quickly, clearly, and objectively. Former SQL Server ...team member Eric L. Brown offers realistic insight into every significant aspect of SQL Server 2005: its new features, architecture, administrative tools, security model, data management capabilities, development environment, and much more. Brown draws on his extensive experience consulting with enterprise users, outlining realistic usage scenarios that leverage SQL Server 2005’s strengths and minimize its limitations. See More

In early 2004, Microsoft released a new set of extensions to SQL Server 2000 called SQL Server 2000 Reporting Services.SQL Server Reporting Services provides a comprehensive platform for creating, managing, securing, scheduling, and outputting data from SQL Server, Oracle, OLE DB and ODBC data sources. When Microsoft created SQL Server Reporting Services they created it as an extendable platform. You can create your own data extensions and your own output extensions. In ...this article, I'll show you how to create your own custom data extensions for SQL Server Reporting Services.See More

The next version of SQL Server (code name Yukon) has extensive support of the Common Language Runtime (CLR).Previous versions of SQL Server (2000 and earlier) had a mechanism for creating custom data types. These data types were nothing more than aliases to system data types. In Yukon, you can create your own fully functional custom data types.

With Visual Studio 2008 and .NET 3.5, developer’s data access options have increased substantially. In addition to using ADO.NET to create DataReaders or DataSets, Microsoft has added LINQ to SQL and Entity Framework as well as ADO.NET Data Services, which leverages those two. In addition to these new options, there are new syntaxes to learn. LINQ, which is built into Visual Basic and C#, has one implementation for LINQ to SQL and another for LINQ to Entities. In Entity ...Framework, you have the option to use LINQ to Entities as well as two other ways of querying with Entity SQL, as you can see in Figure 1.See More

Eventually SQL Server databases in a production environment must move from one server to another. It’s important to plan for eventual database moves when architecting a database solution.Designers and developers often overlook the eventuality that a database will need to move to a different server. This month’s column is the first of three dealing with the issues surrounding database moves. In this first part, you’ll learn about the reasons why you should plan for moving... a database when you design and architect your application.See More

Microsoft released Entity Framework, an ORM (Object Relational Mapping) tool, in 2008.Entity Framework gives developers the ability to be abstracted from the underlying relational database management system and allows them to talk to a database using familiar LINQ-based syntax.

When it comes to analysis and reporting, managers love Excel. Just give them the raw data and they have a field day. For enterprise-level reporting, however, you want everybody to have the same data and the same interpretation of that data. With some effort this can be achieved without having to say goodbye to Excel.

The latest release of Entity Framework gets it geared up for some big future changes. Julie shows you that the changes are nothing to sneeze at.

Wow, another year has gone by, and as you read this, you are probably returning to the office after a few more or less relaxing holiday time spent with friends and family and a New Year's celebration. Interesting things have happened in our industry in the last 12 months, but I predict that the next 12 months will be quite a bit more interesting! Seldom before have I been as excited about new technologies and developments as I am now.

How many times have you found yourself in need of knowing what the next or previous business date is?What about the dates when a particular quarter begins and ends? Have you had to handle the thorny issue of daylight savings time (DST); when does DST begin and end for this or another year in the US, UK or some other region? And finally, once you have determined you have a need to calculate these various date values, where do you host that logic? This article answers those questions.

Basic error handling in SQL Server's programming language, Transact-SQL, is straightforward.But when you nest calls to stored procedures, and the procedures have SQL transactions, error handling becomes much more complex. In this article I'll show you some tried-and-true models for how to handle errors in nested stored procedures with transactions.

May/June 2008 .NET Rocks! column

Heard on .NET Rocks!: Kimberly Tripp on SQL Server 2005 SP1 and More

May/June .Net Rocks Column

Ron Talmage discusses how developers can avaiod Transact-SQL Cursors

Unless your battery is really, really good, you’ll eventually want to store your Ink.In some situations, simple file storage or XML serialization is sufficient for your needs, but in the majority of business applications, you’ll want to move Ink into and out of a relational database. Here’s how.

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 Ser...ver Reporting Services reports.See More

The challenge of bringing data from efficient storage engines such as SQL Server into object-oriented programming models is hardly a new one. Most developers address this challenge by writing complex data access code to move data between their applications and the database. This requires an understanding of the database so that you can access data either from the raw tables, from views, or from stored procedures.</b>

The Microsoft® Sync Framework is the new framework and runtime for adding synchronization, roaming, and offline capabilities to applications. It supports peer-to-peer scenarios, works with devices and services, and is agnostic of data types, stores, and protocols. In this article, I’ll cover the high-level vision for the platform as well as the enabled scenarios made possible by the framework for developers, ISVs, and OEMs.

Tom’s having a lot of fun playing with SQL Server 2005’s T-SQL enhancements.In this article he combines a bunch of them in concert to solve an interesting problem.

With the combined launch of Visual Studio 2008, SQL Server 2008, and Windows Server 2008, Microsoft is introducing five implementations of .NET Language Integrated Query (LINQ).Of these five implementations, two specifically target access to relational databases: LINQ to SQL and LINQ to Entities.

As database developers, many of us have had to dip our feet into the wide ocean of XML.It should come as good news that in SQL Server 2005, you can store XML in the database with a new XML datatype. Although this is good news, many developers have been storing XML in the database for some time now. Without implicit support for XML, developers have been shoving XML documents into text fields since XML’s inception.

Moving a SQL Server database from one server to another is simple-initially. But there are various methods for moving a database, and some have more advantages than others. Investigating the types of moves ahead of time can make planning for a database migration easier.It takes essentially three steps to move a database from one server to another: take the database out of production, copy it to another server, and then bring the copy back into production. While you can c...hoose from many methods to accomplish these steps, it can be difficult to complete the move given all the database’s dependencies. In this article I’ll describe the methods for moving the database. In my next installment I’ll discuss the dependencies and additional related objects that you must also move along with the database data.See More

A new feature of .NET is its ability to handle exceptions. This article demonstrates implementing Microsoft's Exception Management Application Blocks.

xIn the past two installments of this series I’ve discussed two key aspects of moving a SQL Server database from one server (or instance) to another. First I covered the advantages of designing databases to make them easier to move, and then I looked at the methods that you can use to move a database. Now it’s time to look specifically at the types of database dependencies that you must account for after you move a database.

As applications become more complex, designing with components becomes a very important factor in the successful completion of projects.This article discusses n-tier application design, why it's important to modern application development, and shows strategies for breaking out the tiers using Visual FoxPro.

Fifteen years after its launch, ODBC is a firmly entrenched cornerstone of the software industry. This article explains why and will explore the relationship between Microsoft SQL Server and ODBC and discuss where ODBC may go in the future.

Web Services is a powerful technology, even in its basic form.However, with .NET, you can easily couple Web Services with .NET's new data services to provide a powerful data delivery mechanism that works over the Web, making it possible to build distributed applications that work easily without a local data store. In this article, Rick describes various ways you can use Web Services and ADO.NET DataSets to pass data between client and server applications to build truly disconnected applications.

Ron Talmage discusses performance counters in SQL Server.

If you’ve got large datasets, you don’t want to slow the system down every time you save them to your SQL Server database. Rod shows you an uncomplicated way to keep your records zipping along.

SQL Server Katmai, now officially announced as SQL Server 2008, introduces a significant amount of new and improved functionality, including enhanced data types and greater programming flexibility for database application developers.

XML is quickly becoming the preferred method of passing information, not only for the Internet, but also across applications and within applications.Until now, developers have been forced to create our own routines to convert data stored in a database system into XML. With the release of Microsoft SQL Server 2000, however, you can query data directly from SQL Server in XML format.

The point of showing data in graphical form is to make things clear, right? Keven shows you how the best of intentions can go wrong and how to repair the damage.

Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place. In this article, you'll learn how SQL Server deadlocks arise, what types of deadlocks there are, and how you can resolve them.

Ron Talmage discusses scaling out database writes.

Adding indexes to tables is the most effective way to optimize your SQL Server query performance. Tables that you query often may require several indexes in order to handle the various search arguments you send. There is one particular situation where indexes on large tables are a hindrance, though. When you're bulk-copying data into the table, it's best to have either no index at all, or just a clustered index. For those situations it is best to use Transact-SQL scripts... to remove and add those indexes. In this article you'll learn how to use Transact-SQL stored procedures to generate Transact-SQL indexing scripts that are more robust and less laborious than SQL Server's Enterprise Manager scripting tool. See More

SQL Server, like most complex databases, has potential security holes. This article discusses these security holes and how to close them.

One of the major benefits of writing .NET code to run in the Common Language Runtime (CLR) hosted in any environment is code access security (CAS).CAS provides a code-based-rather than user-based-authorization scheme to prevent various kinds of luring and other code attacks. But how does that security scheme coexist with SQL Server 2005’s own, newly enhanced security features? By default your .NET code is reasonably secure, but it’s all too easy for the two security sche...mes to butt heads and cause you grief. In this article I’ll look briefly at the concept behind CAS and a few new security features in SQL Server 2005, then explore how to make the two systems work for you instead of against you as you take advantage of these advanced programming features in SQL Server.See More

Snapshot isolation is a new isolation level that reduces blocking reads and allows transactions to read previously committed versions of data, instead of uncommitted data.SQL Server's default isolation level of READ COMMITTED restricts transactions to reading only data that has been committed. This isolation level has the effect of blocking a transaction's read operation when the data is exclusively locked by another transaction. A lower isolation level called READ UNCOM...ITTED allows your transactions to read another transaction's uncommitted data while it is locked, but the values are not stable because the data is uncommitted. SQL Server 2005 introduces a new isolation level that provides a middle way between READ COMMITTED and READ UNCOMMITTED. The SNAPSHOT isolation level allows your transaction to read the previously committed version of the data that is currently being changed, so that your transaction's view of the data is consistent with the state of the data when your transaction began without being blocked by the other transaction. Snapshot isolation has the benefit of reducing blocking and deadlocking in your transactions that is due to blocking reads, without having to let your transactions read uncommitted data. (Note: this article is based on a pre-Beta 2 version of SQL Server called IDW3, build 9.00.790. Some details about the operation and behavior of snapshot isolation may change before the final version of SQL Server 2005 ships.)See More

In addition to the transaction-based SNAPSHOT isolation level, SQL Server 2005 Beta 2 also introduces a statement-level variation of the READ COMMITTED isolation level called READ_COMMITTED_SNAPSHOT.Part I of this article (see "Snapshot Isolation in SQL Server 2005," July/August, Volume 5, Issue 4) described the transaction-based SNAPSHOT isolation level. At the time Part I was written, just prior to the release of Beta 2, only the SNAPSHOT isolation level was fully docu...mented. With the release of SQL Server 2005 Beta 2, a fuller explanation of a second type of SNAPSHOT isolation behavior has come to light.See More

Replication agents are the basic components of SQL Server 2000 replication.Before you settle on the type of replication you want in your application architecture, it is important to understand how each type of replication works and what agents they use. Since the different types of replication use different combinations of replication agents, understanding what those agents do helps clarify the resulting SQL Agent jobs when you install replication. For example, snapshot,... transactional, and merge replication all use the Snapshot Agent to initialize the subscriber's data. Then they employ other agents to implement the replication process. In this article, you'll learn about the roles of the replication agents and learn how to inspect the properties and profiles of the Snapshot and other agents.See More

Replication figures as one of the more prominent features of SQL Server 2000.Replication is a complex application that uses a combination of stored procedures and executables to distribute and copy data between SQL Server databases. If you take care not to get lost in the details and confused by occasionally misleading terms, replication can be an important component in the database architecture of a SQL Server application. In this article, you'll learn about the termino...logy surrounding replication, the types of replication available in SQL Server, and how to configure (that is, install) replication.See More

One of the classic problems with database applications is refreshing stale data. Imagine a typical e-commerce site with products and categories. A vendor’s product list most likely does not change very often and their category list changes even less frequently. However, those same lists must be queried from the database over and over again every time a user browses to that Web site. This is an annoyingly inefficient use of resources and developers and architects have bee...n stuck playing cat-and-mouse trying to reduce the waste.See More

If you care about your data, you must upgrade to SQL Server 2005 the day it is released. There simply is no other option.An outrageous assertion? Perhaps. I tend to split my time equally between praising and bashing Microsoft, but the new security features and tools in SQL Server 2005 will be mandatory for protecting your data from today's increasingly sophisticated attacks. Most importantly, SQL Server 2005's many layers of security provide for defense in depth in which... layer after layer of protection helps keep data safe.See More

SQL Server 2005 or "Yukon" is going to be a major SQL Server update containing updates to nearly every facet of the program, including T-SQL.In this article I am going to explore some of the new T-SQL features, commands, and capabilities in SQL Server 2005. Because covering everything new in T-SQL would require an entire chapter in a book, I am going to cover some of the more useful and mainstream enhancements.

Ron Talmage discusses SQL Server 2005 in this introductory SQL Server Observer Newsletter

SQL Server 2008 has been out for more than a year, and perhaps an overview and assessment is due. From a database administrator’s point of view, what features are standing the test of time?

Ron Talmage discusses the roles of SQL Server databases and instances in high availability scenarios.

We’ve all heard that version 3 of a Microsoft product is when that product really hits its stride.And while I’m not sure of the truth of that software urban legend, I do have to say that Microsoft, with the release of SQL Server 2008 Reporting Services (which happens to be its 3rd major version), has released a gem of a reporting solution that is sure to please users of prior versions and bring more converts into the Reporting Services fold. The newest version of SQL Ser...ver Reporting Services is faster, can handle larger reports, supports a wider variety of browsers, and is more flexible thanks to the new Tablix data region, support for rich text content, and Microsoft Word as an output format.See More

Kevin looks at his favorite reporting tool (SSRS) and shows you how to maximize your users’ experience.

SQL Server’s Reporting Services is still the best workhorse for getting data to your users. Kevin tells you how to take advantage of some of its more subtle points.

You already know that SQL Server’s Reporting Services tools are useful. Kevin shows you some nifty work-arounds and extra-credit tricks that will make your reports sing.

An important principal of software design is that of “least privilege.”Basically, in any given layer of a program, you should only grant minimal access such that the code has rights to only exactly the resources it needs to get its job done-and nothing more. Most SQL Server developers understand this concept: one of the main reasons to use stored procedures is to encapsulate permission to data behind controlled and auditable interfaces, thereby not giving the caller direct access.

Kevin leaves his SQL Server-focused Baker’s Dozen behind and launches into a new series looking at the various stages of data warehousing. His first installment includes a look at reporting and storage layers, handy tips, and dealing with the business side of development.

SQL Server 2017 has machine learning services baked right in. If you’ve been wondering how to use it, you’ll be fascinated by what Jeannine’s serves up.

Testing SQL Server is an often-overlooked area of the software development process, because programmers primarily place testing focus on code residing in the middle tier or the client desktop, rather than the database. However, as SQL Server databases become a more important component of applications, they cannot be left out of the developer's testing process. An important place to start, to ensure your code works the way you want it to, is with SQL Server configuration....The focus of this column is to examine issues dealing with testing SQL Server 2000 databases using SQL Server's built in language, Transact-SQL, as the primary testing tool.See More

Testing SQL Server 2000 applications begins with making sure your production server is configured properly.You generally do this by verifying that the server's settings match a properly configured target computer. After configuring your server, you can go to work on validating your database configuration. This article will continue the theme of creating and implementing a Transact-SQL stored procedure to test a database's options for deviation from the default settings.

When I was a kid, I loved baseball. I lived it 24/7. In the summertime, happiness meant a pickup game during the day and a Phillies doubleheader at night. I’m still a kid at heart and I still love baseball - and I also love SQL Server. And right now, happiness means seeing all the cool new features in SQL Server 2012. There are so many of them that I can’t list them in a single article. So, I’m penning a two-part Baker’s Dozen. The first part of this “twin-bill” (yes, ex...pect a few baseball analogies!) will be 13 new T-SQL and database engine features in SQL Server 2012. The “night-cap” in the next issue will be 13 new features in SQL Server Integration Services and the new Business Intelligence Semantic Model.See More

In the conclusion of this two-part series on managing data, I will focus on .NET generics, the ASP.NET 2.0 ObjectDataSource, and some more T-SQL 2005 capabilities. Regardless of whether you work in C# or VB.NET, or whether you spend more time on the front-end or back-end of an application, the ability to manage data will directly impact your contributions to a project. The new .NET generics classes provide you with near-quantum leap in efficiency by allowing you to write... simpler code that accomplishes more. ASP.NET 2.0’s ObjectDataSource class helps you to easily integrate middle-tier data-aware classes to data-bound controls on a Web page. Finally, as you saw in Part 1 of this series (and will see again here in Part 2), T-SQL 2005 provides new language capabilities to help database developers be more productive.See More

In part two of this series on optimizing SQL Server queries I’m going to continue with some T-SQL scenarios that pit one approach versus another. I’ll also look at what SQL developers can do to optimize certain data access scenarios. I’ll also compare approaches with temporary tables versus table variables, and stored procedures versus views.

Are you moving a Windows desktop application to the browser, and sweating bullets, or perhaps just not quite sure about how all the new Web and data tools work together?With each passing year, Microsoft offers newer and more powerful tools for building rich database applications on the Web. So many and so frequently, in fact, that it can be hard to keep up with the new tools and still meet the requirements of your job! This article will show you how to get the most out o...f the new features in ASP.NET 3.5. The article will also show how you can use features in LINQ, even if you only use stored procedures for data access. And finally, since most applications use reporting, I’ll throw in a few nuggets on using SQL Server Reporting Services.See More

MDX is just like Transact-SQL, right? MDX is English-like and therefore easy to learn, right? Different software tools offer MDX generators and therefore I don’t need to learn MDX, right? MDX isn’t that popular, right?Well, just like the punch-line of the old Hertz car rental commercial, “Not exactly.” If your organization uses OLAP databases, you can add great value by knowing how to query OLAP databases using MDX queries. This article will cover common OLAP query requi...rements and MDX code solutions.See More

A major software release either contains a substantial number of enhancements, or a small number of substantial enhancements.The last two releases of SQL Server remind me of the two types of Christmas: SQL 2005 was like receiving a large number of presents, and SQL Server 2008 is like receiving a smaller number of big gifts. Those of you who asked Santa for the ability to INSERT/UPDATE/DELETE in one SQL statement, or to specify multiple GROUP BY statements in a single SQ...L query, you will be very happy. If you wanted a hierarchical data type, you will be ecstatic. SQL Server 2008 also includes a new and completely re-written version of SQL Server Reporting Services (SSRS)-complete with a new interface, new charting features, and (get ready to applaud) no more reliance on Internet Information Services (IIS). So while the temperatures are getting warmer, it’s Christmas Day here at the Baker’s Dozen, and we’re about to unwrap thirteen major SQL Server/SSRS 2008 gifts from Microsoft.See More

A skilled database developer might find it difficult to accept that other tools can increase productivity. When Microsoft released SQL Server 2005 Integration Services (SSIS 2005), I did not think that any tool could possibly make me more productive than the C# and T-SQL code I was writing by hand. After some reconsideration (and subtle persuasion from peers), I discovered that SSIS 2005 contained many features that indeed reduced my development time - WITHOUT sacrificin...g flexibility. Microsoft added new functionality in SSIS 2008 to make a strong product even better. In this article, I’ll present 13 different examples that demonstrate the power of Integration Services.See More

Database developers who learn Microsoft SQL Server Analysis Services (SSAS) know that they face a number of learning curves. In prior Baker’s Dozen articles, I’ve covered many of the steps for creating both SSAS OLAP and SSAS Tabular databases. In this article, I’m going to cover another topic: How to add or change data in analytic databases.

All eyes were on Microsoft when the latest version of SQL Server came out a couple of months ago. If you want to know what’s so great about it, you need to read Kevin’s handy summary.

No, the title isn’t a misprint-this installment of the Baker’s Dozen will visit both sides of the planet. These days, many .NET user group meetings focus on database and business intelligence topics as well as hardcore .NET content. Over the last several months, I’ve spent roughly half my time modifying my own development framework for WCF. The result is some basic but functional factory classes I’d like to share. The other half of the time, I’ve been looking at differen...t capabilities in the SQL Server 2005 and 2008 Business Intelligence stack, and solving requirements that BI developers often face. So rather than pick one side and make the other side wait two months, I decided to combine the two.See More

To use a music analogy, many installments of “The Baker’s Dozen” have been like “concept albums,” where most or all of the tips work towards a big picture. Then there are times where I present a series of random tips that are largely standalone and don’t form a pattern. In this article, I’m going to present 13 random tips for SQL Server and T-SQL programming.

Whether you’re new to T-SQL or not, you’re sure to find something interesting among Kevin’s tips for using it, no matter which version of SQL Server you’re using.

Thinking of upgrading to SQL Server 2005? Or are you using SQL 2005 already and you want to learn more about the language features? Then this article may be for you!Microsoft implemented many new features in SQL Server 2005, including an impressive set of language enhancements. From new language statements for SQL-99 compatibility to new features in response to customer requests, Transact-SQL 2005 helps to increase developer productivity. In this article, I’ll cover most... of the new language features by posing a statement/scenario and then provide some code samples to show how you can use T-SQL 2005 to address the problem. At the end of the article, I’ll talk briefly about Visual Studio Team Edition for Database Professionals, a product that helps a development team to manage databases. Finally, I’ll give you a sneak preview of some features in the next scheduled version for SQL Server (SQL Server 2008, “Katmai”).See More

Kevin uses his experience on both sides of the interview table to help you wow at your next interview. Not only that, but you’ll probably pick up a few pointers, too!

Lookup and navigation screens initially seem like no-brainers, when compared to other parts of an application-yet by the time a developer has met all the user requirements and requests, he/she has expended much more time than expected. This issue of The Baker’s Dozen will build a lookup Web page using ASP.NET 2.0, SQL Server 2005, and C# 2.0. The lookup and results page will contain optional lookup criteria and custom paging of the result set. The solution will utilize n...ew language features in SQL 2005 for providing ranking numbers to correlate with custom paging, and new capabilities in .NET generics to pump the results of a stored procedure into a custom collection. Just like Mr. Mayagi taught Daniel the martial arts by doing exercises, the example in this article will demonstrate some common design patterns, such as factory creation patterns, using .NET generics. The article also subtly presents a general methodology for building database Web pages.See More

For several years, database developers have created analytic (OLAP) databases using tools such as Microsoft SQL Server Analysis Services. SSAS includes the MDX programming language for retrieving data and writing custom expressions. These tools remain very powerful for creating analytic applications. However, some view the tools in SSAS/MDX as difficult to learn. In SQL Server Analysis Services 2012, Microsoft has created a second model (known as the tabular model) for c...reating analytic databases. The new model brings the promise of simplified features and (in some cases) even better performance than traditional OLAP. In this article, I’ll provide an overview for this new model and will walk through a basic example of creating an SSAS 2012 tabular model database.See More

Imagine you’re working for a company that has accumulated a tremendous amount of transaction data. The business users want to perform all sorts of analysis, monitoring and analytics on the data. Some OLTP developers might reply with, “Just create views or stored procedures to query all the data the way the users want.” Many companies initially take that approach - however, just like certain technologies and system hardware configurations don’t scale well, certain methodo...logies don’t scale well either. Fortunately, this is where data warehousing and dimensional modeling can help. In this article, I’ll provide some basic information for developers on the basics of data warehousing and dimensional modeling - information that might help you if you want to provide even more value for your company. See More

In this first installment of his ETL articles, Kevin shows you what to do—and even what not to do—when designing your Data Warehouse.

Scorecards, test results, report cards, summaries-nearly everyone wants to skip past the details and see the bottom line. Whether it is “pass or fail”, “go or no go”, managers evaluate professional efforts based on performance. Microsoft’s Business Intelligence tools provide developers and power users with the tools and methodologies to produce scorecards and other summaries that graphically represent performance. This article will create a Web-based Dashboard using seve...ral Microsoft products, including the newest product in the BI stack, PerformancePoint Server.See More

Even with all the new features in the Microsoft SQL Server Business Intelligence (BI), sometimes the only way to accomplish a task is with good old fashioned T-SQL code. (Fortunately, “code” is the acronym for this great magazine!) In this latest installment of The Baker’s Dozen, I’ll present 13 T-SQL programming tips that could help you accomplish different database tasks.

There’s an old programmer adage: “First you make it work, then you make it work fast.” Well, when writing T-SQL queries, you can do both, if you have some knowledge about how the SQL Server optimizer works. This will be a two-part article. In part one, I’ll start with fairly basic optimization tips and techniques for writing SQL queries. In part two, I’ll cover more advanced techniques.

If you’ve ever wondered how your SQL Server knowledge stacked up, you’ll want to take Kevin’s unofficial test. He explains both the right and wrong answers, so no matter what your skills are, you’re bound to learn something new.

Many application developers face the challenges of working with SQL Server 2000.These challenges include retrieving and grouping large amounts of data, building result sets, and tracking changes to data. All require professional strategies that hold water against a seemingly endless number of possibilities. This installment of "The Baker's Dozen" presents a variety of real-world database situations and how you can use Transact-SQL and SQL-92 to tackle these issues.

“Integrate the data tier developer in to the core development life cycle and process.”That is one of the main objectives of Visual Studio Team Edition for Database Professionals, also known under its project name “Data Dude”. Bringing the data tier developer into Visual Studio is the first step in enabling closer integration between the application and data tier developer. Having both environments leverage the same Team Foundation Build (Team Build) system enables daily ...and automatic integration of changes into the build process, enforcing closer integration and shorter feedback cycles between the two originally disjoint disciplines.See More

In this article you will learn how to isolate yourself from change by taking advantage of the Provider Model.Designing your applications using the Provider Model will allow you to swap components out at runtime, thus allowing you to upgrade them easily.

Ron Talmage discusses 32-bit vs. 64-bit version of SQL Server 2005

SQL Server 2000 is packed full of useful system stored procedures there for its own use that you can use as well.This article will explore a couple dozen of the most useful hidden gems you can use to more easily manage your databases, improve the applications that use the data, and provide new ways to impress potential mates at parties. You'll learn when to use these system stored procedures, explore when to use them, and see lots of code.

Before you start hard coding parts of your project to work with metadata in SQL Server, make sure that the functionality you want isn’t already part of the product.

A well-designed application that uses a relational database management system in the backend should make extensive use of stored procedures.A stored procedure is a named collection of SQL statements that you store in a database. To the client, a stored procedure acts similar to a function. You call the stored procedure by name, you can pass it parameter values, and it can return parameter values back to your client.

The richest set of cloud computing services comes from a little e-commerce company known as Amazon.com. Developers can access the Amazon Web Services (AWS) platform using numerous tools including the .NET platform.Amazon.com is a major player in the cloud computing space and has numerous services available to developers. In late 2009, Amazon released the AWS SDK for .NET. This article will demonstrate using the AWS SDK to create a custom backup service using the Amazon S...imple Storage Service (S3).See More

If you have been working with the ADO.NET Entity Framework, you have probably been extremely eager to get your hands on the next version that is now part of Visual Studio 2010 and .NET 4.0. Long referred to as “EF Version 2,” this version is now called Entity Framework 4 or EF4, to align with the .NET Framework 4.0 version.

If you have been working with the ADO.NET Entity Framework, you have probably been extremely eager to get your hands on the next version that is now part of Visual Studio 2010 and .NET 4.0. Long referred to as “EF Version 2,” this version is now called Entity Framework 4 or EF4, to align with the .NET Framework 4.0 version.

As XML becomes more and more prolific in the world of data exchange it's increasingly important that data can be quickly and easily extracted from XML documents and moved into more permanent data stores.Although .NET offers several different ways for performing this task, the XmlTextReader represents the most efficient and scalable solution.

The new XML features in SQL Server 2000 give the developer more power to implement distributed solutions.One of the newest features, XML UpdateGrams, allows the developer to handle the inserting, updating and deleting of records while getting around some of the limitations of URL queries and OPENXML. XML UpdateGrams perform their operations against an XML view, which is provided by an annotated XDR schema that contains the necessary information to map elements and attrib...utes back to their corresponding tables and fields.See More

Imagine the potential of accessing your database without needing to know any specifics about how that data is stored.Imagine the possibility of allowing your DBA the freedom to change the database structure without affecting your software code. Impossible? Not with XPath Queries and XDR Schemas. Using these two methods, practically any developer can write a program to access data in SQL Server 2000, just by knowing the structure of the XML documents returned by the server.