DBMS vs RDBMS
RDBMS = DBMS + Referential Integrity
SQLServer 2000 vs SQLServer 2005
- In SQL Server 2000 the Query Analyzer and Enterprise Manager are seperate,whereas in SQL Server 2005 both were combined as Management Studio.
- In Sql 2005,the new datatype XML is used,whereas in Sql 2000 there is no such datatype
- The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.
- SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000
Normalization
Normalization is the process of efficiently organizing data in a database.
First Normal Form (1NF) - Eliminate duplicative columns from the same table.
Second Normal Form (2NF) - Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
Third Normal Form (3NF) - Remove columns that are not dependent upon the primary key.
Fourth Normal Form (4NF) - A relation is in 4NF if it has no multi-valued dependencies.
Joins
ReplyDelete-----
Inner Join
Ex:-
Use Vendor
GO
SELECT v.VendorId, v.VendorFName, v.VendorLName, a.royality, a.advance
FROM dbo.Vendor as v
INNER JOIN advance as a
ON v.VendorId = a.VendorId
WHERE v.VendorId <= 5
GO
Outer Join
NULL will appear in columns if there is no matching
Ex:-
Use Vendor
GO
SELECT VendorFName, Vendor.VendorId, VendorLName, Advance
FROM Vendor LEFT JOIN advance
ON Vendor.VendorId = advance.VendorId
GO
Use Vendor
GO
SELECT VendorFName, advance.VendorId, VendorLName, Advance
FROM Vendor RIGHT JOIN advance
ON Vendor.VendorId = advance.VendorId
GO
For Triggers
ReplyDelete=============
best resource
http://www.dotnetspark.com/kb/657-how-to-create-trigger.aspx
what is tablespace in database ?
ReplyDeleteA tablespace is a group of datafiles. Tablespaces can be created, deleted, and merged, just as can the folders on the hard drive of a computer.
CASCADING referential integrity in sql server
ReplyDelete=============================================
By using CASCADE, once the record is deleted or updatd in parent table(with primary key), the record in child table(with foreign key) is also updated and deleted.
1)CASCADE on DELETE
2)CASCADE on UPDATE
CREATE TABLE EmpMaster(EmpId INT PRIMARY KEY,EmpName VARCHAR(25));
CREATE TABLE EmpDetails(EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId) ON DELETE CASCADE,DeptId INT PRIMARY KEY,DeptName VARCHAR(20));
Good one.
ReplyDelete