Articles

Technical Article

Using a Subquery in a T-SQL Statement

Sometimes the criteria for determining which set of records will be affected by a SELECT, UPDATE, DELETE and/or INSERT statement cannot be obtained by hard coding the selection criteria. Occasionally there is a need to use the results of a SELECT statement to help determine which records are returned or are affected by a T-SQL statement. When a SELECT statement is used inside another statement, the inside SELECT statement is known as a subquery. Subqueries can help to dynamically control the records affected by an UPDATE, DELETE or INSERT statement, as well as to help determine the records that will be returned by a SELECT statement. This article will show different examples of how to use a subquery to help clarify the records affected or selected by a T-SQL statement.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-02-11

4,571 reads

SQLServerCentral Article

Monitoring Blocks

SQL Server excels at quickly acquiring and releasing locks to allow as much concurrency as possible on the server. However there are times that one user will block another, which can be a source of great user frustration as well as making the system appear to be slow. Leo Peysakhovich brings us some great information and code on how you can monitor and deal with blocking on your SQL Server 2000 server.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(4)

You rated this post out of 5. Change rating

2005-02-10

17,096 reads

Technical Article

Full Text Search on SQL 2000 Part 3

In the previous month's articles, Full Text Searching was introduced as a way to query strings with more refinement than the usual TSQL "like" or equal operator statements. With Full Text Searching, a new file system structure is created, storing key words from selected fields into Catalogs. In addition to storing typical character fields from databases, Microsoft Office documents that have been saved as binaries can also be entered into the Catalogs. In this month's edition, we will begin with the maintenance issues required to keep these Catalogs current. Once created, these catalogs can be interrogated for key words that are near each other's proximity, the singular and plural versions of a word, or the noun and verb variations of a word. In addition, search result rankings, or weights, that usually accompany internet search engine returns can also be requested.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-02-10

2,147 reads

Technical Article

Rebuilding SQL Server Cluster Nodes

Active/Passive SQL Server 2000 clustering gives more reliability and fault tolerance to Production SQL Server environments. When a failure occurs, all of the resources fail over from the active node to the passive node and make the passive node active. This article explains how to rebuild the node that failed and attach it back to the cluster.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-02-10

2,232 reads

SQLServerCentral Article

Optimizer Join Methods

Understanding the different types of joins used by the optimizer will help developers and DBAs understand how the optimizer is routing their queries. Developers often create queries without knowing that it would only take a few tweaks to produce an execution plan that utilizes one optimizer join method over another. These small tweaks can have dramatic effects on the optimization of the query and the ultimate satisfaction of the query by the end-users.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(1)

You rated this post out of 5. Change rating

2005-02-09

22,628 reads

Technical Article

Full Text Search on SQL 2000 Part 2

In last month's article, Full Text Searching was introduced as a way to query strings with more refinement than the usual TSQL "like" or equal operator statements. With Full Text Searching, a new file system structure is created, storing key words from selected fields into Catalogs. In addition to storing typical character fields from databases, Microsoft Office documents that have been saved as binaries can also be entered into the Catalogs. Once created, these catalogs can be interrogated for key words that are near each other's proximity, the singular and plural versions of a word, or the noun and verb variations of a word can all be searched for. In addition, search result rankings, or weights, that usually accompany internet search engine returns can also be requested.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-02-09

1,986 reads

Technical Article

SQL Server 2005 T-SQL Enhancements

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.

2005-02-09

2,825 reads

SQLServerCentral Article

Freeware: SQL Digger 1.0

Ever been in the situation where you know that you used a function in a stored procedure and couldn't remember how to use it? SQL Server is a great database platform, but it's easy to lose track of all your code as an application grows. A new freeware utility is available to help you search your databases for code.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-02-08

5,312 reads

SQLServerCentral Article

Generating a Bar Code

SQL Server is asked to do many tasks, including scheduling processes, transferring data using DTS in an ETL process, sending mail, updating other systems, generating reports and more. However this one might a bit unsual: using SQL Server and T-SQL to generate a bar code. Ivaca Masar brings us this unique look at how you can stretch the limits of T-SQL.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-02-08

13,955 reads

Blogs

Word to the wise: don’t turn on SQL vulnerability checks on a blog about SQL

By

Recently, in an attempt to thwart leechers (1990s kids will know), I added some...

Dazzling Stuff in Databricks (SQL) – Qualify

By

Once upon a time I had a series about some cool stuff in Snowflake:...

Advice I Like: Overnight Success

By

“Most overnight successes – in fact any significant successes – take at least 5...

Read the latest Blogs

Forums

Why Successful SQL Server Backups Are Not Enough

By Almighty

Comments posted to this topic are about the item Why Successful SQL Server Backups...

Adding Bits II

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Adding Bits II

An Unlimited Token Budget

By Steve Jones - SSC Editor

Comments posted to this topic are about the item An Unlimited Token Budget

Visit the forum

Question of the Day

Adding Bits II

I have a table, dbo.bittest, where I am testing an application response. This has this structure:

CREATE TABLE [dbo].[BitTest]
(
[insertdate] [datetime] NULL CONSTRAINT [DF__BitTest__insertd__300F11AC] DEFAULT (getdate()),
[a] [bit] NULL
) ON [PRIMARY]
GO
My application runs this code:
INSERT dbo.BitTest
(
    insertdate,
    a
)
VALUES
(DEFAULT, true)
GO

See possible answers