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 »
|
By host on
12/3/2009 1:20 PM
Z7GCU4QKCHW5
Read More »
|
By host on
12/2/2009 3:14 PM
What is SQL Injection?
It is a type of attacking technique by changing the SQL code in your website. If your web application was not developed carefully and wisely, then there is a chance that attackers can do SQL Injections and mess up your database. They may alter the SQL code and steal your vital information from the database. Not only getting information from your database, they can also delete and modify information or modify the structure of your database.
Read More »
|
By host on
12/2/2009 2:56 PM
Starting in SQL Server 2005, the exception handling has been very similar to the exception handling in c++ and c#. SQL Server 2005 introduced the Try and Catch blocks. You can place all your T-SQL statements in the TRY block. If an error occurred in any T-SQL statements within the TRY block, then the control will jump to the nearest CATCH block. In the CATCH block you can do any extra processing (e.g., rollback any remaining transactions) and if you want to re-throw the error, then you can use RAISERROR command. But if there was no error in the TRY block, the CATCH block will be skipped.
Read More »
|
By host on
12/2/2009 1:39 PM
What is a view?
The View is a saved SQL statement and referred as a virtual table. When you create a view, Microsoft SQL Server stores only metadata information about the view, describing the object, its columns, security, dependencies, and so on.
Read More »
|
By host on
12/2/2009 1:24 PM
The SQL code which is executed dynamically is called Dynamic Sql. The Dynamic SQL can help programmers to achieve things that would be difficult to achieve otherwise. The dynamic SQL queries are usually important in search pages where the number of input parameters varies depending on the search criteria selected on the search page. However, dynamic SQL could lead to serious security breaches, extra overheads to run and difficulties to maintain.
Read More »
|