Modern Data Insight

Following steps used to delete duplicate records 1. Identify duplicates select au_lname, au_fname, city, state, count(*) from dup_authors group by au_lname, au_fname, city, state having count(*) > 1 order by count(*) desc, au_lname, au_fname 2. Temp Table and Truncate select distinct * into #holding from dup_authors 3. Rename and Copy Back sp_rename 'dup_authors', 'temp_dup_authors' 4. Create a Primary Key (prevention) -- Add a new column -- In real life I'd put an index on it Alter table dup_authors add NewPK int NULL Article from SQLTeam, more

SQL Server CE is available as a download for use with SQL Server 2000 from the Microsoft Web site and as part of Microsoft''s eMbedded Visual Tools 3.0. If you don''t have SQL Server 2000 and just want to take SQL Server CE out for a spin, use the eMbedded Visual Tools version. Both come with the Windows CE version of Query Analyzer, which provides a way to create and administer databases on the Windows CE device. Those unfamiliar with Query Analyzer will find its GUI relatively intuitive. Article by Edmond Woychowsky, more

The SQL Server Web Data Administrator enables you to easily manage your SQL Server data, wherever you are. Using its built-in features, you can do the following from Microsoft Internet Explorer or your favorite Web browser: - Create and edit databases in SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine - Perform ad-hoc queries against databases and save them to your file system - Export and import database schema and data - Manage users and roles View, create and edit stored procedures Article by Steven Warren, more

Following 10 things you should know about SQL Server 2005 features : 1. There is now an XML data type 2. Distributed Management Objects (DMO) becomes SQL Server Management Objects (SMO) 3. Common Table Expresssions recursive queries 4. The Service Broker makes SQL Server traffic asynchronous 5. Create .NET triggers 6. SQL Server 2005 configuration is dynamic 7. Define your own data types 8. Many active result sets, one connection 9. WAITFOR ... RECEIVE 10. DTS is now Integration Services Article by Scott Robinson, more

This is a C# Open source version of SQL Query Analyzer with code completion - If you type in a table name and hit ctrl-space, it has a dropdown with the columns, etc. I need a version that works from usb drive. Source SQL Buddy .

This article describes how to programmatically extract and store Excel formulas in a SQL Server database and replicate the UI-side calculations using dynamic SQL. In Part 1 of this article series shows how to take advantage of XML maps in Excel to create a template for data entry. Stored procedures are then used to perform aggregations for parent locations and update calculated data as well. You can extend the sample code in this article as far as needed for most scenarios that require support of flexible XML mapping and Excel formulas applied to aggregate values. Currently a variation of this code in a project called "The Rhythm of the Business 2.0," which supports a flexible and constantly changing Excel template used by more than 1,000 employees. Article from msdn, more

This article reviews how locking plays a role in SQL Server and how locking can lead to potential blocking problems. It looks at the typical methods currently in use to identify blocking as well as their advantages and disadvantages. Also introduces the Sherlock tool and its uses in quickly and graphically identifying blocking. Article by Roberto Alexis Farah (msdn), more

Locks are the mechanism databases use to control problems that may arise from simultaneous multi-user access. Some common issues locks try to manage include; what happens when two users try to change the same piece of information at the same time - who wins? Or user one tries to read data while user two is making changes on the same information, - which version of the data should user one see, changed or unchanged? Without locks, these situations may cause the data or results of a query to be logically incorrect. Lock sophistication is a key difference between simple databases designed for single user applications and enterprise databases capable of simultaneously handling large volumes of multi-user actions. Article by Don Schlichting, more

When an error is encountered within a stored procedure, the best you can do (assuming its a non-fatal error) is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. Notice that the previous sentence is specific to non-fatal errors. There are two type of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error. Article by Garth Wells, more

SQL is implemented in different architecture. Different DBMS products handle various SQL (and related) features differenlty. The goal of this article — is to gather information relevant for people who are porting SQL from one product to another and/or are interested in possibilities and limits of 'cross-product' SQL. Article by Troels Arvin, more

SQL is the most popular computer language used to create, modify and retrieve data from relational database management systems. The language has evolved beyond its original purpose to support object-relational database management systems. The most frequently used operation in transactional databases is the data retrieval operation. Technically, SQL is a declarative computer language for use with "relational databases". Theorists note that many of the original SQL features were inspired by, but in violation of, tuple calculus. Article from Wikipedia, more

Idera's SQLcheck quickly investigates, organizes and explains critical diagnostic information about your server's hardware, operating system and SQL Server. With SQLcheck's performance dashboard, you can quickly view detailed explanations and receive recovery suggestions. Get the information you need to efficiently manage your SQL Server environment. Make sure you have admin rights to the sql box. more tags: database , sql

SQL Server 2000 clustering can add much needed reliability and fault-tolerance to a production SQL Server 2000 system. But as you might imagine, its installation and configuration is not as easy an installing SQL Server 2000 on a single server. The purpose of this article is to show you how to install SQL Server 2000 Enterprise Edition onto a Windows 2000, two-node cluster using an Active/Passive configuration. Installing SQL Server 2000 clustering is not an easy task. It requires a good knowledge of Windows 2000, SQL Server 2000, and time. Article by Brad M. McGehee (SQL-Server-Performance)

Avoid publishing unnecessary data. Try to restrict the amount of published data. This can results in good performance benefits, because SQL Server will publish only the amount of data required. This can reduce network traffic and boost the overall replication performance. Article from MS SQL City, more

This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption" There are 2 versions: one for SP's only and the other one for SP's, triggers and views version 1: INPUT: object name (stored procedure, view or trigger) version 2: INPUT: object name (stored procedure, view or trigger), object type('T'-trigger, 'P'-stored procedure or 'V'-view). Code by Joseph Gama, more

A database maintenance plan is implemented using a series of DBCC commands and system stored procedures. DBCC stands for database consistency check or database console command. There are numerous documented and undocumented DBCC commands, but only a few of them are used for maintenance. A typical database maintenance schedule involves the following activities. 1. Check database consistency and data integrity (DBCC CHECKDB) 2. Rebuild indexes or remove index fragmentation (DBCC DBREINDEX) 3. Update statistics (DBCC SHOW_STATISTICS) 4. Report on space utilization in data and log files (DBCC SHRINKFILE) Article by Kevin Kline and Baya Pavliashvili from SearchSQLServer, more

All UDFs are defined using the CREATE FUNCTION statement, which varies slightly for scalar, in-line, and multi-statement functions. Scalar UDFs have the following syntax: CREATE FUNCTION owner.function_name (parameter_name data_type [=default] [, n]) RETURNS scalar_data_type [WITH function_option] AS BEGIN Function_body RETURN scalar_expression END Article by Baya Pavliashvili, more Recommended Book:

User-defined functions (UDF) allow you to add custom solutions for unique application-specific problems. Tasks can be as simple as calculating a value or as complex as defining and implementing table constraints. Technically speaking, SQL Server UDFs are subroutines of encapsulated Transact-SQL (T-SQL) logic that you can call into action via other T-SQL code to return a scalar (single) value or a table variable. more

Following article by Alexander Chigrik talks about useful undocumented stored procedures. Most frequest ones I use - sp_MShelpcolumns, sp_MShelpindex etc. more

Reserved Keywords are part of the grammar of the Transact-SQL language used by SQL Server to parse and understand Transact-SQL statements and batches. Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only using delimited identifiers. It is recommended to not use the reserved keywords as identifiers or object names. more