By host on
3/30/2010 12:19 PM
What is LINQ?
Language Integrated Query (LINQ) is a Microsoft .Net Framework component which adds native date querying capabilities to .Net languages. LINQ unifies the way data can be retrieved in .Net, from any object which implements the IEnumerable interface.
Read More »
|
By host on
3/17/2010 11:25 AM
While writing stored procedures it is often needed to store data temporarily in one place and perform some manipulations on it so that the expected output can be acquired. For example, when the result set of a complex query pulling data from one or more database tables, we need a table-like object to store them in order to reuse it wherever we need it. Microsoft SQL server has provided two kinds of database objects to store data temporarily. They are temporary tables and table variables.
Read More »
|
By host on
3/17/2010 10:08 AM
In SQL Server, there are two datetime columns. Those are Datetime and Smalldatetime.
DATETIME Column
The date range for a DATETIME column can be January 1, 1753 to December 31, 9999. SQL Server uses 8 bytes to store the DATETIME data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1753. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.
SMALLDATETIME Column
The date range for a SMALLDATETIME column can be January 1, 1900 to June 6, 2079. SQL Server uses 4 bytes of storage for SMALLDATETIME column. The first 2 bytes integer piece contains the number of days since January 1, 1900 and the second 2 byte integer holds the number of minutes since midnight.
Read More »
|
By host on
3/17/2010 9:59 AM
I had come across a requirement to search a string in all columns of all tables of a database with several varchar columns each, looking for various string data. I had to find a stored procedure to search text through all tables. It snakes through the schema of your selected database looking for all chars, varchars, nchars, and nvarchars (Text datatype has been eliminated for performance) looking for my string. It returns the name of the table and column, and the containing text block. I ran it on our staging system and it found my string in about 30 seconds... not too shabby.
Read More »
|
By host on
3/16/2010 2:23 PM
This is Part 2 of our Top 8 Useful and Important Scripts in SQL. Please feel free to leave your useful scripts in the comments.
Read More »
|
By host on
3/16/2010 2:11 PM
Over the last few months, we have been writing articles to try to assist you in your SQL Server ventures. We have decided to compile a list of our top 8 useful and important scripts of the last couple of months. Please feel free to use this as a resource to improve your productivity. If you have other useful scripts that you want to share, please feel free to post them in the comments.
Read More »
|
By host on
3/12/2010 11:05 AM
A stored procedure is a group of Transact-Sql statements compiled into a single execution plan. Stored procedures offer huge benefits
- We can Manage, control, and validate data
- They can be used for access mechanisms
- We can avoid large queries
- They reduce network traffic since they need not be recompiled
- Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
- We can set the permissions for stored procedures, hence implementing security
Read More »
|
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 »
|