Relational database management system (RDBMS)

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.

A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

Not be enough virtual memory - SQL Server

For each updated database, SQL Server allocates at least one 64-KB block for use in formatting log records before they are written to disk. This allocation occurs when the first log record is generated for the database, such as during an INSERT, UPDATE, or DELETE statement. Depending on the activity and the size of the generated log records, subsequent modifications might trigger the allocation of additional 64-KB allocations. SQL Server 7.0 will allocate no more than three 54-KB blocks. In SQL Server 2000, the upper number of allocations for each database is a function of the number of processors that SQL Server is configured to use.

Solution
Use the -g startup parameter to leave additional, unreserved virtual memory available for these database allocations. The -g parameter is documented in the Readme.txt of the SQL Server 7.0 service pack, and in SQL Server 2000 Books Online. The "More Information" section in this article includes the settings that Microsoft recommends you use to determine the appropriate value for this setting.

Sql Server 2005 - Twelve Tips For Optimizing Query Performance

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

Original Article

LINQ - .NET Language Integrated Query

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.

Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.

LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources. The "Orcas" version of the .NET Framework ships with built-in libraries that enable LINQ support against Objects, XML, and Databases.

For More details about the LINQ :
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
Part 6: Retrieving Data Using Stored Procedures
Part 7: Updating our Database using Stored Procedures
Part 8: Executing Custom SQL Expressions
Part 9: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the control)

SQL IDENTITY_INSERT

SQL IDENTITY_INSERT is allow values to be inserted into the identity column of a table.
Identity column creates a numeric sequence value for you automatic.

It only alow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.

SET IDENTITY_INSERT TableName OFF

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

SET IDENTITY_INSERT TableName ON

SQL Get Rows Number

Below SQL Statement return rows number for each row in a partition.

select R1.CategoryName,
rank = (select count(distinct R2.CategoryName) from categories R2 where R1.CategoryName <= R2.CategoryName) from categories R1 Order by Rank

Below is the result after use the row number sql statement:

DBCC DBREINDEX

When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.

Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.

The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database

TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.

Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)

Example
DBCC DBREINDEX ('Categories')
DBCC DBREINDEX ('Categories','Categoryname',80)

- It Can improve your database perfomance.

WITH RECOMPILE - Re Compile Execution Query Plan

WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, you can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.

Below is the simple example for using With Recompile in creating STORE PROCEDURE
CREATE PROCEDURE sp_MyTable WITH RECOMPILE
AS
Select Column1, column2 from Table
GO

Except the above method, you also can using with Recompile when u Execute the Store procedure in Query Analyzer.

Below is the example for using With Recompile in Query analyzer
EXEC sp_MyTable1 'Parameter1', 'Parameter2' WITH RECOMPILE

This tells SQL Server to throw away the existing query plan or do not cache the previous execution query plan and build another one query plan but only this once.

sp_changedbowner - SQL Change DB Owner

- Changes the owner of the current database.

After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.

The owner of the master, model, or tempdb system databases cannot be changed.
To display a list of the valid login values, execute the sp_helplogins stored procedure.
Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.

Only members of the sysadmin fixed server role can execute sp_changedbowner.


Example
This example makes the user Emil the owner of the current database and maps
existing aliases to the old database owner to Emil.

EXEC sp_changedbowner 'Emil'

Overview of DTS Package

DTS Introduction

Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. Data Transformation Services (DTS) addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by DTS connectivity. By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement solutions tailored to the specialized business needs of your organization.

DTS Basics

Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.

DTS supplies a number of tasks that are part of the DTS object model that can be accessed graphically, through DTS Designer, or programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation, and notification situations. For example:

Importing and exporting data
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
Transforming data
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
Copying database objects
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Sending and receiving messages to and from other users and packages
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
Executing a set of Transact-SQL statements or Microsoft ActiveX scripts against a data source
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.

SQL Server 2008 New DATETIME DataTypes

The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. IN addition to these newly introduced data types, there are new DATETIME functions all well.

TIME Datatype
DECLARE @dt as DATE
SET @dt = getdate()
SELECT @dt

23:48:04.0570000

The range for the DATE datatype is from 00:00:00.0000000 through 23:59:59.9999999..


DATETIME2 Data Type
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
SELECT @dt7
2007-10-28 22:11:19.7030000

for above example, (7) is the Fraction

Fraction Output
0 2007-10-28 22:11:20
1 2007-10-28 22:11:19.7
2 2007-10-28 22:11:19.70
3 2007-10-28 22:11:19.703
4 2007-10-28 22:11:19.7030
5 2007-10-28 22:11:19.70300
6 2007-10-28 22:11:19.703000
7 2007-10-28 22:11:19.7030000

Maps an existing database user to a SQL Server login using SP_CHANGE_USERS_LOGIN

Automatically mapping a user to a login, creating a new login if it is required
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.

EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'

- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.

AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.

extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction

Identify your SQL Server version and edition

This article show you your current Microsoft SQL Server version number and the corresponding product or service pack (SP) level. It also describes how to identify the specific edition Microsoft SQL Server.

SELECT @@VERSION
Result - Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Result - 8.00.2039 , SP4 , Standard Edition

SQL SET ROWCOUNT

SQL SET ROWCOUNT statement gives administrators some control over the execution of "runaway" queries issued by naive users of SQL. This statement stops the execution of a query as soon as a specified number of rows has been retrieved. In this way, system resources are not wasted, but users can see at least a partial result set. An informational message is displayed after the result set, indicating that SET ROWCOUNT is in effect.
If a fully executed query happens to return the exact number of rows specified by the ROWCOUNT value, the query-termination message is still displayed.

Example of SQL SET ROWCOUNT
SELECT * FROM EMPLOYEES
- It will return 10 rows of records.

After add SET ROWCOUNT
SET ROWCOUNT 5
SELECT * FROM EMPLOYEES
- It will return 5 rows of records instead of 10 row.
(Bacause you set the RowCount to 5)

SQL SET ROWCOUNT

SQL Split Function

This SQL Split Function is use to SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.

Below is Split Function in SQL
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

Result
- SQL
- TUTORIALS

* Copy blue color Sql split function with change @String variable to test the result in your query analyzer.

SQL Split Function

SQL Alternative Count

Both SQL Query below returns same result but the speed of retrieving data are different.
Copy Below Both Sql Query to Query analyzer to test it. Customers is a Table Name.

1. SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Customers') AND indid <>
You can use sysindexes system table to get number of ROWS. This column contains the total row count for each table in your database. So, you can use the following select statement instead of Below one:

2. SELECT Count(*) FROM Customers
This SQL query performs full table scan to get the row count.


So, you can improve the speed of the SQL query using the Above First SQL instead of Second SQL Query

SQL Alternative Count

SQL Server DataType

All Sql DataType

bigint
Integer data from -2^63 through 2^63-1

binary
Fixed-length binary data with a maximum length of 8,000 bytes

bit
Integer data with either a 1 or 0 value

char
Fixed-length character data with a maximum length of 8,000 characters

datetime
Date and time data from January 1, 1753, through December 31, 9999,with an accuracy of 3.33 milliseconds

decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

float
Floating precision number data from -1.79E + 308 through 1.79E + 308

image
Variable-length binary data with a maximum length of 2^31 - 1 bytes

int
Integer data from -2^31 through 2^31 - 1

money
Monetary data values from -2^63 through 2^63 - 1

nchar
Fixed-length Unicode data with a maximum length of 4,000 characters

ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 characters

nvarchar
Variable-length Unicode data with a maximum length of 4,000 characters

numeric
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

real
Floating precision number data from -3.40E + 38 through 3.40E + 38

smalldatetime
Date and time data from January 1, 1900, through June 6, 2079,with an accuracy of one minute

smallint
Integer data from -2^15 through 2^15 - 1

smallmoney
Monetary data values from -214,748.3648 through +214,748.3647

sysname

text
Variable-length data with a maximum length of 2^31 - 1 characters

timestamp

tinyint
Integer data from 0 through 255

varbinary
Variable-length binary data with a maximum length of 8,000 bytes

varchar
Variable-length data with a maximum of 8,000 characters

uniqueidentifier
A globally unique identifier



SQL SET ROWCOUNT
SQL Split Function
SQL Alternative Count
SQL Server DataType
SQL WHILE Statement
SQL CASE Function
SQL IF...ELSE Statement
SQL First and Last Day Of a Month
SQL Statement - get Total Working Day in a month
SQL EXISTS
SQL Subquery
SQL UNION ALL
SQL Union
SQL Mathematic Functions
SQL String Functions
SQL CAST and CONVERT
MySql Total Day of Month
SQL DATEDIFF Function
SQL Dateparts and Abbreviations
SQL DATEADD Function
SQL Statement Last Day of a month
SQL Aggregate Function - Syntax and Example
SQL DATENAME Function - Year, Quarter, Month, Day,...
SQL Inner Join, Left Join and Right Join
Get Number of Row in Select Statement
Select Statement for Get Number of Days in a Month...
SQL Statement Example for SELECT, UPDATE and DELET...
SQL Statement Example for UPDATE and DELETE
All Topic in Sql Tutorials and ProgrammingSchools
SQL Syntax - SELECT,UPDATE and DELETE
What is SQL?

SQL WHILE Statement

SQL While sets a condition for the repeated execution of an SQL statement or statement block. The SQL statements are executed repeatedly as long as the specified condition is return TRUE. The WHILE loop can be controlled from inside the loop with the CONTINUE, BREAK and GOTO keywords.

BREAK statement will exit you from the currently processing WHILE loop.
GOTO statement will break out of all WHILE loops, no matter how many nested WHILE statements.
CONTINUE statement will skips executing the rest of the statements between the CONTINUE statement and the END statement of the current loop and starts executing at the first line of the current WHILE loop.

WHILE Syntax
WHILE Boolean_expression
{ Sql Statement Block }


Below is simple example of WHILE Statement
DECLARE @counter INT
SET @counter = 0
WHILE @counter <>
   BEGIN
      SET @counter = @counter + 1
      PRINT 'The counter : ' + CAST(@counter AS CHAR)
   END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 4
The counter : 5


Below is example of WHILE Statement with CONTINUE and BREAK
- It show you the using of Continue and Break in WHILE Statement and the IF...ELSE Statement.
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <>
   BEGIN
      SET @Counter = @Counter + 1
      IF @Counter <>
         PRINT 'The counter : ' + CAST(@Counter AS CHAR)
      ELSE IF @Counter > 3 AND @Counter <>
         BEGIN
            CONTINUE
            PRINT 'No Counter Here'
      END
      ELSE IF @Counter > 13 AND @Counter <>
         BREAK
      ELSE
         PRINT 'The counter : ' + CAST(@Counter AS CHAR)
   END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 10
The counter : 11
The counter : 12
The counter : 13


Below is example of WHILE Statement with CONTINUE, BREAK and GOTO
- It show you the using of Continue, Break and Goto in WHILE Statement and the IF...ELSE Statement.
DECLARE @N1 INT
DECLARE @N2 INT
SET @N1 = 0
SET @N2 = 0
WHILE @N1 <>
   BEGIN
   SET @N1 = @N1 + 1
      WHILE @N2 <>
         BEGIN
         SET @N2 = @N2 + 1
         IF @N2 = 3 and @N1 = 1
            GOTO BREAK_OUT
         ELSE
            PRINT 'Value N1 is ' + CAST(@N1 AS CHAR(1)) + ' Value N2 is ' + CAST(@N2 AS CHAR(1)) END
         SET @N2 = 0
      END
   BREAK_OUT:
Value :
Value N1 is 1 Value N2 is 1
Value N1 is 1 Value N2 is 2

SQL CASE Function

SQL Case evaluates a list of conditions and returns one possible result expressions.

CASE has two formats:
1. Simple CASE Function - Compares an expression to determine the result.
2. Searched CASE Function - Evaluates a set of Boolean expressions to determine the result.

CASE Syntax
1. Simple CASE function:
CASE input_expression
WHEN when_expression THEN Result
ELSE result_expression
END

2. Searched CASE function:
CASE
WHEN Boolean_expression THEN Result
ELSE result_expression
END

1. Simple CASE Function
Evaluates input_expression and find the match with when_expression. If found, it will return the Result and if not found, it will return the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Type varchar(20)
SET @Type = 'Programming'

SELECT
   CASE @Type
      WHEN 'Sql' THEN 'sqltutorials.blogspot.com'
      WHEN 'Programming' THEN 'programmingschools.blogspot.com'
      WHEN 'Travel' THEN 'travelyourself.blogspot.com'
      ELSE 'Not yet categorized'
      END
Value = programmingschools.blogspot.com

If SET @Type = 'Picture', then Return value = Not yet categorized



2.Searched CASE Function
Evaluates Boolean_expression for each WHEN clause and returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, SQL Server returns the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Price integer
SET @Price = (20-9)

SELECT
   CASE
      WHEN @Price IS NULL THEN 'Not yet priced'
      WHEN @Price < color="#ff0000">THEN 'Very Reasonable Price'
      WHEN @Price >= 10 AND @Price < color="#ff0000">THEN 'Reasonable Price'
      ELSE 'Expensive book!'
   END
Value = Reasonable Price

If SET @Price = (30-1), then return Value = Expensive book!

SQL IF...ELSE Statement

SQL IF...ELSE Statement used to test a condition. IF...ELSE Statement using in execution of a Transact-SQL statement (Store Procedure or T-SQL) and Trigger.
IF tests can be nested after another IF or following an ELSE. There is no limit to the number of nested levels.

IF condition is satisfied and the Boolean expression returns TRUE, it will executed IF Block Sql statement.
IF condtion is not satisfied and the Boolean expression returns FALSE, it will executed ELSE Block Sql Statement query.

Syntax for IF...ELSE
IF ( Boolean_expression )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
Sql Statement Block
END

Below is simple example of IF...ELSE Statement With 1 IF...ELSE Block
For Boolean_expression part, you can replace with your condition to match with your Sql query.
It also can using Exists in the Condition to check the existence of a Sql Statement or Row.
IF (3>2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END
Value = Correct

IF (3<2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END
Value = Wrong

Below is another Example of More than 1 IF...ELSE Sql Statement Block
IF (10<2)
   SELECT 'Correct'
ELSE
   SELECT 'Wrong'
   IF ((10-2) > 5)
      SELECT 'More Than 5'
Value = Wrong
Value = More Than 5

If your IF...ELSE Block Sql Statement only have 1 Sql query, you no need to include the BEGIN...END. If your IF...ELSE Block Sql Statement have more than 1 Sql query, you need to include the BEGIN...END to let it executed all the Sql Query in your Sql Query Block.