By host on
3/2/2010 10:50 AM
Transact-SQL (T-SQL) is Microsoft's enhanced version of the Structured Query Language (SQL). The Transact-SQL language was introduced by Sybase. It includes transaction control, enabling conditional processing, error handling, row processing, declared variables and more. When T-SQL is written and run, it can ultimately impact your server’s performance.
Read More »
|
By host on
2/18/2010 10:27 AM
What is SQL Index?
An index in a SQL database is similar to the index, or table of contents, in a book. The index in a book helps to quickly search for a particular topic instead of flipping through all the pages of the book. Likewise, when a database has large volume of data, then retrieving particular data from the database consumes more time and thus it becomes a performance issue. Creating indexes on the columns that are frequently accessed can be a good solution to retrieve the data quickly.
Indexes can be created on one or more fields of a table. Indexes are not applicable to the fields of large object (LOB) data types like text, image, and varchar(max).
Read More »
|
By host on
1/21/2010 2:01 PM
User-Defined function (UDF) was introduced in SQL Server 2000. UDF can be used in a complex T-SQL Query, and will be used in problems that were impossible or required cursors can now be solved with UDFs.
Advantages
- UDFs can be used in complex logic within a query
- UDFs can be used to create new functions for complex expressions.
- UDFs offers the benefits of views because they can be used in the “from clause” of the select statement. User defined functions accepts parameters whereas views cannot.
- UDFs offer the benefits of stored procedures because they are compiled and optimized in the same way.
Read More »
|
By host on
1/18/2010 2:39 PM
Sql Joins are used to query data from two or more tables, based on the relationship between columns in the tables. Join condition defines the way two tables are related in a query by:
- Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
- Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.
Different Types of SQL Joins
- JOIN: Return rows when there is at least one match in both tables
- LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
- RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
- FULL JOIN: Return rows when there is a match in one of the tables
Read More »
|
By host on
1/8/2010 10:32 AM
Triggers are the type of stored procedures and will be attached to the tables. We can not execute the triggers directory. Triggers will be fired only in repose to Delete, Insert and Update event on the table. Sql Server 2005 has two different types of transaction triggers. They are instead of triggers and after triggers
Read More »
|
By host on
1/5/2010 12:43 PM
The XML data type (specified as XML) is a built-in data type just like varchar, int, and others. You use it the way you'd use any other SQL Server data type. Columns, variables, parameters, and values that functions return can all use the XML data type. A user can create a table that has one or more columns of type XML in addition to relational columns.
Typed or Untyped XML
Like other datatypes, the XML datatype must meet specific formatting criteria. When the XML data is associated with an XML schema collection, it is called "typed XML"; otherwise it is called "untyped XML". Both typed and untyped XML are accommodated within a single framework, the XML data model is preserved, and query processing enforces XML semantics.
Read More »
|
By host on
1/5/2010 12:26 PM
The datagrid and other data controls provide the paging functionality. That type of paging works fine when the number of rows to be displayed is not too large. But when we have a lot of rows (a hundred thousand or a million rows), that paging is pretty slow and consumes a lot of resources. This article should analyze how we can pass the page number and the number of rows per page to a stored procedure and have it returns only the rows that fall in that page. CTE is pretty suitable for this functionality. The query using the CTE must be the first query appearing after the CTE.
Read More »
|
By host on
1/5/2010 11:20 AM
Database
A database is a structured collection of records that is stored in a computer system. It contains a set of related database objects
- Tables - Storages of structured data.
- Views - Queries to present data from tables.
- Indexes - Sorting indexes to speed up searches.
- Stored Procedures - Predefined SQL programming units.
- Users - Identifications used for data access control.
- Other objects.
System Databases
System Databases will be created during the time of installation. They will help SQL Server to all the other databases and client execution sessions. There are four default system databases.
- master - It is the main database of the SQL server – Server configuration details and runtime information will be stored.
- model – It is a empty database. It is used to create the new databases
- msdb - It used to background job processes
- tempdb – For temporary operations
Read More »
|
By host on
12/23/2009 12:55 PM
Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP?
Read More »
|
By host on
12/23/2009 12:21 PM
You can use the PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where necessary on any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. Every PIVOT query involves an aggregation of some type.
Read More »
|