Difference between synchronous and asynchronous communication?

Asynchronous:
-asynch comm there is no need to establish a connection before data transmission.
-asynch comm is used in packet swithed networks
-widely used for PC communication and is commonly used for e-mail applications, Internet access, and asynchronous PC-to-PC communications.
-data is transmitted one byte at a time with each byte containing one start bit, eight data bits, and one stop bit, thus yielding a total of ten bits.
- Disadvantage: overhead because every byte sent contains two extra bits (the start and stop bits) and therefore a substantial loss of performance.

Synchronous:
-first connection will be established and the communication
will take place.
-synch comm is used in circut swithed netwoks.
-data is transmitted as frames of large data blocks rather than bulky individual bytes. One advantage of synchronous is that control information is easily inserted at the beginning and end of each block to ensure constant timing, or synchronization.
-Another advantage of synchronous is that it is more efficient than asynchronous. For example, a 56 Kbps dial-up synchronous line can carry 7000 bytes per second (56000/8) compared to a 56 Kbps dial-up asynchronous line which can only carry 5600 bytes per second (56000/10).When transmitting large amounts of information, this translates into a significant increase in speed and performance.

Web Part Life Cycle:

- OnInit – Configuration values set using WebBrowsable properties and those in web part task pane are loaded into the web part.
- LoadViewState – The view state of the web part is populated over here.
- CreateChildControls – All the controls specified are created and added to controls collection. When the page is being rendered for the first time the method generally occurs after the OnLoad() event. In case of postback, it is called before the OnLoad() event. We can make use of EnsureChildControls() - It checks to see if the CreateChildControls method has yet been called, and if it has not, calls it.
- OnLoad
User Generated Event – for e.g. button click on the web part.
- OnPreRender – Here we can change any of the web part properties before the control output is drawn.
- RenderContents – Html Output is generated.
- SaveViewState - View state of the web part is serialized and saved.
- Dispose
- UnLoad.

Query for getting the Constraint Information

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

ALTER TABLE Insurance.Contract DROP CONSTRAINT FK_Contract_AccountID

Multi Table Update

select * from A
select * from B

UPDATE A
SET F2 = B.F4
FROM A, B
WHERE A.F1 = B.F3 and A.F2 is null

Trigger Example.

Sample Trigger

CREATE TABLE dbo.Test (
item varchar(50)
)
GO

CREATE TABLE dbo.Test1 (
item varchar(50)
)
GO

Alter TRIGGER InsertEntry ON dbo.Test AFTER INSERT,UPDATE AS

DECLARE @item int

IF EXISTS(SELECT item FROM inserted)
BEGIN
DECLARE @msg varchar(500)
SET @msg = (SELECT item FROM inserted)
insert into test1 values(@msg)
END
GO

Creating Table, Populating the Dates, Retrieving the Weekends in SQL Server 2005

Below Script Create the DateLookup Table:

CREATE TABLE DateLookup
(
DateKey INT PRIMARY KEY,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MonthName VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WeekDay TINYINT,
MonthNumber TINYINT
)

The below Script Populate the Dates in to the lookup table:

DECLARE @Date DATETIME
SET @Date = '1/1/2009'

WHILE @Date < '1/1/2011'
BEGIN
INSERT INTO DateLookup
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MonthName, YearDay,
DateDefinition,
CharacterDate,
WeekDay,
MonthNumber
)
SELECT
CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
DATENAME(mm, @Date) + ' \' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',\'
+ CAST(DATEPART(yy, @Date) AS CHAR(4)),
CONVERT(VARCHAR(10), @Date, 101),
DATEPART(dw, @Date),
DATEPART(mm, @Date)

SET @Date = DATEADD(dd, 1, @Date)
END


To Get the WeekEnds(Saturdays & Sundays) Use the Below Query:

select * from DateLookup where datepart(w,DateFull) in (1) or datepart(w,DateFull) in (7)

SQL Server: Date Formats

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
Result : 2009/03/05

SELECT CONVERT(VARCHAR(10), GETDATE(), 11) AS [YY/MM/DD]
Result : 09/03/05

SELECT CONVERT(VARCHAR(30), GETDATE(), 131)as Datetime
Result : 9/03/1430 2:05:32:957PM

SELECT CONVERT(VARCHAR(22), GETDATE(), 100)as datetime
Result : Mar 5 2009 2:06PM

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
Result :03/05/2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
Result :03-05-09

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
03-05-2009

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
Result :Mar 05, 09

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
Result :Mar 05, 2009

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
Result :05 Mar 09

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
Result :05 Mar 2009


SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
Result :05.03.09

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
Result :05.03.2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
Result :05-03-09


SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
Result :05-03-2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
Result :05/03/09

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
Result :05/03/2009