|
|
 |
String User-Defined Functions
|
| Total Hit (2625) |
Introduction
String UDFs
StrIns
StrDel
StrSeparate
StrCHARINDEX
StrREPLACE
StrREVERSE
«B»Introduction«/B»
I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:
==>Date and Time User-Defined Functions
==>Mat
....Read More |
Rating
 |
|
|
|
|
|
|
|
Working with Computed column in SQL Server 2000
|
| Total Hit (1542) |
There are restriction for ALTER COLUMN, documented in Books Online (see end
of this post). Depending on your particular situation, you need to DROP
INDEX, ALTER TABLE... DROP CONSTRAINT etc.
The altered column cannot be:
a) A column with a text, image, ntext, or timestamp data type.
....Read More |
| Rating
|
|
|
|
Selective Defrag/Reindex and Log
|
| Total Hit (3120) |
SQL 2000 Only
This script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:
1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)
2. Defragment all user indexes in the dat
....Read More |
Rating
 |
|
|
|
|
|
|
|
|
|
How to find start/end date of Previous Quarter
|
| Total Hit (3345) |
«code LangId=6»declare @PrevQtrDate datetime
declare @PrevQtrStartDate datetime,@PrevQtrEndDate datetime
declare @ReportDate datetime
set @ReportDate=getdate()
set @PrevQtrDate=dateadd(q,-1,@ReportDate)
Print @PrevQtrDate
set @PrevQtrStartDate=cast(month(@PrevQtrDate) as varchar(10)) + '
....Read More |
| Rating
|
|
|
|
Understanding the TOP WITH TIES clause in SELECT queries
|
| Total Hit (3033) |
The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group. To see what this means in practice, execute the following query against the Pubs database in SQL Server 7.0:
«Code LangId=6»
SELECT TOP 5 price, titl
....Read More |
| Rating
|
|
|
|
|
|
|
|
How to improve performance with pinned tables
|
| Total Hit (2524) |
SQL Server experts know that the DB engine never accesses directly the data pages on disk; rather it uses a special module called "Cache Manager". It is possible, by using some T-SQL commands, to force the selected tables to stay in the buffer for a longer time. This technique increases the performa
....Read More |
| Rating
|
|
|
|
Maximum Value from 5 columns.
|
| Total Hit (1411) |
This script shows how to obtain the maximum value of 5 columns. It is possible to do this using CASE statement but the expression gets quite lengthy. This shows an easy way to use the MAX function itself & this approach can be extended to as many columns as you want.
....Read More |
| Rating
|
|
|
|
Passing a CSV or Array to a Stored Procedure
|
| Total Hit (1882) |
This stored procedure takes two parameters @ARRAY and @SEPARATOR. It loops through the array variable and pulls out the values inside it. Right now the stored procedure just prints the values it finds. It's written so as to be easily customizable to do exactly what you need it to. Enjoy and happy pa
....Read More |
| Rating
|
|
|
|
Advanced Lock Monitoring
|
| Total Hit (2895) |
In order to assist continuous and effective monitoring of lock status, SQL Server offers the system stored procedure SP_LOCK, which can provide several useful pieces of information on how locks are set and removed.
However, using this information - such as process ID, database ID, and object ID -
....Read More |
| Rating
|
|
|
|
|
|
Enhanced SQL Server Security Auditing
|
| Total Hit (1372) |
Security audit in SQL server captures only successful and failed logins. It does not capture the application that uses the login. Login used by an application may have full read and write access on all of the tables and procedures but the application restricts the users by providing a front-end whic
....Read More |
| Rating
|
|
|
|
MDX Essentials: Basic Set Functions: The EXCEPT() Function
|
| Total Hit (773) |
This is the fifteenth article of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.
....Read More |
| Rating
|
|
|
|
Connecting to an AS400 with DTS
|
| Total Hit (563) |
DTS does not support AS400 connectivity natively, but by obtaining a third party ODBC or OLE-DB provider you can connect quite easily. In no particular order, some links for suppliers of such providers:
|
| Rating
|
|
|
|
Introduction to SQL : Sample chapter from Beginning SQL Programming
|
| Total Hit (694) |
This introductory chapter covers topics that students usually ask in the first hour of classes; essentially, just what do we mean by SQL? We start by explaining what SQL is and also what it is not, and we provide a brief overview of the history of the language. We'll also clarify some confusing term
....Read More |
| Rating
|
|
|
|
MDX Essentials: Structure of the MDX Data Model
|
| Total Hit (653) |
This is the second article of the series, MDX Essentials. The primary focus of this series will be an introduction to the MDX language. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively addi
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
SQL Server Full Text Search Optimization
|
| Total Hit (1703) |
Full text indexing is a great feature that solves a long running database problem, the searching of textual data columns for specific words and phrases in SQL Server databases.
|
| Rating
|
|
|
|
Linking SQL Server to Heterogeneous Systems
|
| Total Hit (1972) |
The ability to link SQL Server to heterogeneous systems such as Oracle, DB2, Sybase, etc., is imminent. This article provides the basic and most important information required to make a linked server connection to heterogeneous systems from SQL Server. We are going to deal with three different RBBMS
....Read More |
| Rating
|
|
|
|