Search a specific column in Sql Server Database

To find a specific column in a huge database system is pretty hectic and you miss out the columnat all sometimes and later decide to give it up or trapped in some hectic process. Therefore to get a better solution to this just try this query and it will show you the tables where to look at for that specific column:

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'ColumnName' )

Self Join

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields.

A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.

Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor.

Please refer the sample database for table structure.

SELECT DISTINCT pv1.ProductID, pv1.VendorID

FROM Purchasing.ProductVendor pv1

INNER JOIN Purchasing.ProductVendor pv2

ON pv1.ProductID = pv2.ProductID

AND pv1.VendorID = pv2.VendorID

ORDER BY pv1.ProductID

Example:

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

For self join in sql you can try the following example:

Create table employees:

emp_id emp_name emp_manager_id
1 John Null
2 Tom 1
3 Smith 1
4 Albert 2
5 David 2
6 Murphy 5
7 Petra 5

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:

select e1.emp_name 'manager',e2.emp_name 'employee'

from employees e1

join employees e2

on e1.emp_id=e2.emp_manager_id

manager employee
John Tom
John Smith
Tom Albert
Tom David
David Murphy
David Petra

Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1

join employees e2

on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Create User-Defined Data Types in SQL Server

User-defined data types are based on the system data types in Microsoft® SQL Server™ 2000. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability. For example, a user-defined data type called postal_code could be created based on the char data type. User-defined data types are not supported in table variables.

When a user-defined data type is created, you must supply these parameters:

• Name
• System data type upon which the new data type is based
• Nullability (whether the data type allows null values)

When nullability is not explicitly defined, it will be assigned based on the ANSI null default setting for the database or connection.

Note If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.

Syntax:
sp_addtype [ @typename = ] type,

[ @phystype = ] system_data_type

[ , [ @nulltype = ] 'null_type' ]

[ , [ @owner = ] 'owner_name' ]

http://msdn.microsoft.com/en-us/library/aa933121(SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa259606(SQL.80).aspx

Example:
sp_addtype doj , datetime
create table tblTest (id int,dob doj)

__________________________________________________________________
User-defined types can either be created manually using Enterprise Manager or using a system stored procedure in Transact-SQL. In Enterprise Manager, simply select the “User Defined Data Types” category for the database, right click and select New User Defined Data Type. Then just complete the dialog box that appears.

In Transact-SQL, use the sp_addtype stored procedure, e.g.

EXEC sp_addtype TTelephoneNum, 'varchar(24)', 'NOT NULL'

The user-defined type can now be used in place of any of the standard types in table and procedure definitions.

Example:

CREATE TABLE PhoneBook (EntryID int IDENTITY (1,1) NOT NULL, PhoneNum TTelephoneNum, FaxNum TTelephoneNum);

CREATE PROCEDURE PhoneBookAdd(@PhoneNum TTelephoneNum, @FaxNum TTelephoneNum)
AS
INSERT INTO PhoneBook (PhoneNum, FaxNum) VALUES (@PhoneNum, @FaxNum);
GO

What is Magic/ Scanned Tables in SQL Server

Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the insert and the delete tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.Whenever any updation takes place,the trigger uses both the inserted and deleted tables.