Sql Code Masterz

Composite Key : A composite key is a combination of more than one column to identify a unique row in a table. Exp : EmpID, EmailID, SSN in Employee table and project ID in project table .if EmpID and project id are put in projectionHours table then combination of empid and project ID called as composite key because combination of these two act as primary key in projectionHours table. Candidate Key: All keys in a table that become unique called as candidate key. Exp : EmpID , EmailID and SSN all will be always unique for any employee in that case all these three columns called as candidate keys. Alternate Key: Among of candidate keys if any single key or combination of keys made as primary key then rest candidate key called as alternate key. Exp : Suppose in employee table EmpID is primary key then Emailid and SSN are called as alternate key mean later on these key can be act as primary key without affecting existing data in table.

It depends on Version SQL Server 2005: Maximum tables can be 256 SQL Server 2008: Depends on resource availability SQL Server 2008 R2: Depends on resource availability You can confirm by using script given below /*Creating 300 Tables for testing*/ Use [Master] Go Declare @I Int, @Script Varchar(500) Select @I = 1 While (@I <=300) Begin Select @Script = 'Create Table Table' + CAST(@I as varchar) + '(Id Int)' Exec(@Script) Select @I = @I + 1 End Go /*Using all the tables in SELECT statement*/ Use [Master] Go Declare @I Int, @Script Varchar(Max) Select @I = 1 Select @Script = 'Select A1.* From ' While (@I <=299) Begin if (@I >1) Select @Script = @Script + ' Join Table' + CAST(@I+1 as varchar) + ' A' + CAST(@I+1 as varchar) + ' On (' + 'A' + CAST(@I+1 as varchar) + '.Id=' + ' A' + CAST(@I as varchar) + '.Id)' else Select @Script = @Script + 'Table' + CAST...

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys . A key formed by combining at least two or more columns is called composite key .

User defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8) . In this case you could create a user defined data type called Flight_num_type of varchar(8) and use it across all your tables.

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key does not allow NULL s, but unique key allows one NULL only.

There are mainily 3 types of triggers DML trigger Instead of trigger written on only view System trigger DML trigger written on table that gets fired on dml events like insert or delete or update operation that trigger may fire  After DML event  Before DML event these are timings for trigger firing Instead of trigger written only on view that view is not modifiable that also gets fired when DML event occurs System trigger gets fired on system event like before log off or after log on or before shutdown this trigger  may be written on   Database level   Schema level  --------------------------------------------------------------------------------------------------------- OR you can see as follows --------------------------------------------------------------------------------------------------------- Based on timing: 1. Before 2. After 3. Instead of Based on Event: 1. DDL (Insert, update, delete ) 2. DML ( Cre...

Reduce memory footprint of a .NET application   try { Process process = Process . GetCurrentProcess (); process . MaxWorkingSet = loProcess . MaxWorkingSet ; process . Dispose (); } catch { }