18 September 2010

SQL PRIMARY KEY

SQL PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

SQL PRIMARY KEY Constraint on ALTER TABLE

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

To DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID


To see the Constraint of table use <> in sql server

SQL FOREIGN KEY

SQL FOREIGN KEY

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)


To DROP a FOREIGN KEY Constraint

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

SQL PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.

A primary key column cannot contain NULL values.
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Meaning For Dotnet First line of Code

Meaning For Dotnet First line of Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
@ Page directive on top of page
Page Language="C#" Denotes C# language is used in this project

AutoEventWireup attribute defaults to true,

ASP.NET does not require that you explicitly bind event handlers to a page event such as Load.

When AutoEventWireup is false, you must explicitly bind the event to a method. For example, if you have a Page_Load method in the code for a page, the method will be called in response to the Load event only if you write code like that in the following example

Otherwise you have to bind the event explicitly

public partial class AutoEventWireupExample : System.Web.UI.Page
{
protected void Page_Load(object sender, System.EventArgs e)
{
Response.Write("Executing Page_Load");
}
override protected void OnInit(EventArgs e)
{
this.Load += new System.EventHandler(this.Page_Load);
}
}

The following code example shows how to set or read the AutoEventWireup property in code.
// Get the current AutoEventWireup property value.
Console.WriteLine(
"Current AutoEventWireup value: '{0}'",
pagesSection.AutoEventWireup);

// Set the AutoEventWireup property to false.
pagesSection.AutoEventWireup = false;


The following example shows the two forms of method signatures that are automatically attached to page events when AutoEventWireup is true.

protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Hello world");

}
// This method will be automatically bound to the Load event
// when AutoEventWireup is true only if no overload having
// object and EventArgs parameters is found.
protected void Page_Load()
{
Response.Write("Hello world");
}



// Following are three alternative ways of binding an event
// handler to an event when AutoEventWireup is false. For
// any given event do this binding only once or the handler
// will be called multiple times.

// You can wire up events in the page's constructor.
public _Default()
{
Load += new EventHandler(Page_Load);
}

// You can override the OnInit event and wire up events there.
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
Load += new EventHandler(Page_Load);
}

// Or you can override the event's OnEventname method and
// call your handler from there. You can also put the code
// execute when the event fires within the override method itself.
protected override void OnLoad(EventArgs e)
{
Page_Load(null, null);
base.OnLoad(e);
}

protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Hello world");
}

Inherits="_Default"

the Inherits attribute tells the runtime the name of the class it will use as a base class for this web form,

look at the CodeFile (code-behind) file for the ASPX page.

using System;
using System.Web.UI;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
}
‘partial’ keyword.
Partial allows us to split a class definition across two or more source code files.
The partial keyword plays an important role because it will allow the runtime to extend the definition of our _Default class with additional members.

Difference between Stored procedure vs User Functions in Sql server

Difference between Stored procedure vs User Functions in Sql server

• Procedure may return none or more values. Function must always return one value either a scalar value or a table.

• A user-defined function takes zero or more input parameters and returns either a scalar value or a table.

• Procedure have input,output parameters.Functions have only input parameters.

• Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.


• Functions can be called from procedure. Procedures cannot be called from function.


• Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)


• Transaction management possible in procedure but not in function.

Difference Between SCOPE_IDENTITY and ROWCOUNT in sql server

Difference Between SCOPE_IDENTITY and atatROWCOUNT in sql server

SCOPE_IDENTITY

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

ROWCOUNT"

Returns the number of rows affected by the last statement.

How to convert from string to date / datetime? in sql server

Execute the following T-SQL scripts in Microsoft SQL Server Manangement Studio Query Editor to demonstrate T-SQL convert and cast functions in transforming string date, string time & string datetime data to datetime data type. T-SQL date / datetime functions usage examples are presented as well.

-- SQL Server string to date / datetime conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date

-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000



-- Without century (yy) string date conversion - convert string to datetime function

SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000



-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date


SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

-- 1900-01-01 20:10:44.000



-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

-- YYYYMMDD ISO date format works at any language setting - international standard

SELECT convert(datetime, '20161023')

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

-- 2016-10-23 20:44:11.500


-- Style 126 is ISO 8601 format: international standard - works with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)

-- 2008-10-23 18:52:47.513


-- Convert DDMMYYYY format to datetime - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc

SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default

SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.

SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI

SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR

SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German

SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian

SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.

SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.

SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec

SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.

SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan

SELECT convert(datetime, '161023', 12) -- yymmdd ISO

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt

SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

------------



-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss

-- String to datetime - mssql datetime - sql convert date - sql concatenate string

DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)



SELECT @DateValue = '20120718',

@TimeValue = '211920'

SELECT @DateTimeValue =

convert(varchar, convert(datetime, @DateValue), 111)

+ ' ' + substring(@TimeValue, 1, 2)

+ ':' + substring(@TimeValue, 3, 2)

+ ':' + substring(@TimeValue, 5, 2)

SELECT

DateInput = @DateValue, TimeInput = @TimeValue,

DateTimeOutput = @DateTimeValue;

/*

DateInput TimeInput DateTimeOutput

20120718 211920 2012/07/18 21:19:20

*/


/* Datetime 8 bytes internal storage structure
o 1st 4 bytes: number of days after the base date 1900-01-01

o 2nd 4 bytes: number of milliseconds since midnight */

-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm

DECLARE @Seconds INT

SET @Seconds = 20000

SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)

/* HH MM SS

5 33 20 */
------------



-- SQL Server 2008 convert datetime to date - sql yyyy mm dd

SELECT TOP (3) OrderDate = CONVERT(date, OrderDate),

Today = CONVERT(date, getdate())

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY newid();

/* OrderDate Today

2003-07-09 2012-06-18

2003-09-26 2012-06-18

2004-02-15 2012-06-18 */

------------

-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

/* YYYY/MM/DD

2015/07/11 */

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]

/* YYYYMMDD

20150711 */

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD]

/* YYYY MM DD

2015 07 11 */
-- Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
-- 07-MAR-14
------------

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM

PRINT CONVERT(datetime,'07-10-2012',110) -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'2012/07/10',111) -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'20120710', 112) -- Jul 10 2012 12:00AM

------------



-- String to date conversion - sql date yyyy mm dd - sql date formatting

-- SQL Server cast string to date - sql convert date to datetime

SELECT [Date] = CAST (@DateValue AS datetime)

-- 2012-07-18 00:00:00.000



-- SQL convert string date to different style - sql date string formatting

SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100)

-- May 8 2014 12:00AM

-- SQL Server convert date to integer

DECLARE @Date datetime; SET @Date = getdate();

SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);

-- Result: 20161225



-- SQL Server convert integer to datetime

DECLARE @iDate int

SET @iDate = 20151225

SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)

-- 2015-12-25 00:00:00.000



-- Alternates: date-only datetime values

-- SQL Server floor date - sql convert datetime

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))

-- SQL Server cast string to datetime

-- SQL Server datetime to string convert

SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)

-- SQL Server dateadd function - T-SQL datediff function

-- SQL strip time from date - MSSQL strip time from datetime

SELECT getdate() ,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

-- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000

-- String date - 10 bytes of storage

SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110)

SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110)

-- Same results: 01-02-2012



-- SQL Server cast datetime as string - sql datetime formatting

SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012 3:47AM


----------

-- SQL date range BETWEEN operator

----------

-- SQL date range select - date range search - T-SQL date range query - sql date ranges

-- Count Sales Orders for 2003 OCT-NOV

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = convert(DATETIME,'10/01/2003',101)

SET @EndDate = convert(DATETIME,'11/30/2003',101)



SELECT @StartDate, @EndDate

-- 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000

SELECT DATEADD(DAY,1,@EndDate),

DATEADD(ms,-3,DATEADD(DAY,1,@EndDate))

-- 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997



-- MSSQL date range select using >= and <

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM Sales.SalesOrderHeader

WHERE OrderDate >= @StartDate AND OrderDate < DATEADD(DAY,1,@EndDate)

/* Sales Orders for 2003 OCT-NOV

3668 */



-- Equivalent date range query using BETWEEN comparison

-- It requires a bit of trick programming

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM Sales.SalesOrderHeader

WHERE OrderDate BETWEEN @StartDate AND DATEADD(ms,-3,DATEADD(DAY,1,@EndDate))

-- 3668



USE AdventureWorks;

-- SQL between string dates

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN '20040201' AND '20040210' -- Result: 108



-- SQL BETWEEN dates without time - time stripped - time removed - date part only

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE DATEDIFF(dd,0,OrderDate)

BETWEEN DATEDIFF(dd,0,'20040201 12:11:39') AND DATEDIFF(dd,0,'20040210 14:33:19')

-- 108

-- BETWEEN is equivalent to >=...AND....<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000'

/* Orders with OrderDates

'2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM)

'2004-02-10 00:01:00.000' - 1 minute after midnight

'2004-02-10 01:00:00.000' - 1 hour after midnight

are not included in the two queries above. */

-- To include the entire day of 2004-02-10 use:

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= '20040201' AND OrderDate < '20040211'

----------

-- Date validation function ISDATE - returns 1 or 0 - SQL datetime functions

------------

DECLARE @StringDate varchar(32)

SET @StringDate = '2011-03-15 18:50'

IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)

PRINT 'VALID DATE: ' + @StringDate

ELSE

PRINT 'INVALID DATE: ' + @StringDate

GO

-- Result: VALID DATE: 2011-03-15 18:50



DECLARE @StringDate varchar(32)

SET @StringDate = '20112-03-15 18:50'

IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)

PRINT 'VALID DATE: ' + @StringDate

ELSE

PRINT 'INVALID DATE: ' + @StringDate

GO

-- Result: INVALID DATE: 20112-03-15 18:50

-- First and last day of date periods - SQL Server 2008 and on code

DECLARE @Date DATE = '20161023'

SELECT ReferenceDate = @Date

SELECT FirstDayOfYear = CONVERT(DATE, DATEADD(yy, DATEDIFF(yy,0, @Date),0))

SELECT LastDayOfYear = CONVERT(DATE, DATEADD(yy, DATEDIFF(yy,0, @Date)+1,-1))

SELECT FirstDayOfSemester = CONVERT(DATE, DATEADD(qq,((DATEDIFF(qq,0,@Date)/2)*2),0))

SELECT LastDayOfSemester = CONVERT(DATE, DATEADD(qq,((DATEDIFF(qq,0,@Date)/2)*2)+2,-1))

SELECT FirstDayOfQuarter = CONVERT(DATE, DATEADD(qq, DATEDIFF(qq,0, @Date),0))

-- 2016-10-01

SELECT LastDayOfQuarter = CONVERT(DATE, DATEADD(qq, DATEDIFF(qq,0, @Date)+1,-1))

-- 2016-12-31

SELECT FirstDayOfMonth = CONVERT(DATE, DATEADD(mm, DATEDIFF(mm,0, @Date),0))

SELECT LastDayOfMonth = CONVERT(DATE, DATEADD(mm, DATEDIFF(mm,0, @Date)+1,-1))

SELECT FirstDayOfWeek = CONVERT(DATE, DATEADD(wk, DATEDIFF(wk,0, @Date),0))

SELECT LastDayOfWeek = CONVERT(DATE, DATEADD(wk, DATEDIFF(wk,0, @Date)+1,-1))

-- 2016-10-30



------------

-- Selected named date styles
------------

DECLARE @DateTimeValue varchar(32)

-- US-Style

SELECT @DateTimeValue = '10/23/2016'

SELECT StringDate=@DateTimeValue,

[US-Style] = CONVERT(datetime, @DatetimeValue)



SELECT @DateTimeValue = '10/23/2016 23:01:05'

SELECT StringDate = @DateTimeValue,

[US-Style] = CONVERT(datetime, @DatetimeValue)



-- UK-Style, British/French - convert string to datetime sql

-- sql convert string to datetime


SELECT @DateTimeValue = '23/10/16 23:01:05'

SELECT StringDate = @DateTimeValue,

[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)



SELECT @DateTimeValue = '23/10/2016 04:01 PM'

SELECT StringDate = @DateTimeValue,

[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)



-- German-Style

SELECT @DateTimeValue = '23.10.16 23:01:05'

SELECT StringDate = @DateTimeValue,

[German-Style] = CONVERT(datetime, @DatetimeValue, 4)



SELECT @DateTimeValue = '23.10.2016 04:01 PM'

SELECT StringDate = @DateTimeValue,

[German-Style] = CONVERT(datetime, @DatetimeValue, 104)

------------



-- Double conversion to US-Style 107 with century: Oct 23, 2016

SET @DateTimeValue='10/23/16'

SELECT StringDate=@DateTimeValue,

[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)



-- Using DATEFORMAT - UK-Style - SQL dateformat

SET @DateTimeValue='23/10/16'

SET DATEFORMAT dmy

SELECT StringDate=@DateTimeValue,

[Date Time] = CONVERT(datetime, @DatetimeValue)

-- Using DATEFORMAT - US-Style

SET DATEFORMAT mdy

-- Convert date string from DD/MM/YYYY UK format to MM/DD/YYYY US format
DECLARE @UKdate char(10) = '15/03/2016'
SELECT CONVERT(CHAR(10), CONVERT(datetime, @UKdate,103),101)

-- 03/15/2016

-- DATEPART datetime function example - SQL Server datetime functions

SELECT * FROM Northwind.dbo.Orders

WHERE DATEPART(YEAR, OrderDate) = '1996' AND

DATEPART(MONTH,OrderDate) = '07' AND

DATEPART(DAY, OrderDate) = '10'



-- Alternate syntax for DATEPART example

SELECT * FROM Northwind.dbo.Orders

WHERE YEAR(OrderDate) = '1996' AND

MONTH(OrderDate) = '07' AND

DAY(OrderDate) = '10'
------------

------------

-- T-SQL calculate the number of business days function / UDF - exclude SAT & SUN

------------

CREATE FUNCTION fnBusinessDays (@StartDate DATETIME, @EndDate DATETIME)

RETURNS INT AS

BEGIN

IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN (0)

DECLARE @i INT = 0;

WHILE (@StartDate <= @EndDate)

BEGIN

SET @i = @i + CASE

WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1

ELSE 0

END

SET @StartDate = @StartDate + 1

END -- while

RETURN (@i)

END -- function

GO

SELECT dbo.fnBusinessDays('2016-01-01','2016-12-31')

-- 261

------------

-- T-SQL DATENAME function usage for weekdays

SELECT DayName=DATENAME(weekday, OrderDate), SalesPerWeekDay = COUNT(*)

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY DATENAME(weekday, OrderDate), DATEPART(weekday,OrderDate)

ORDER BY DATEPART(weekday,OrderDate)

/* DayName SalesPerWeekDay

Sunday 4482

Monday 4591

Tuesday 4346.... */



-- DATENAME application for months

SELECT MonthName=DATENAME(month, OrderDate), SalesPerMonth = COUNT(*)

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY DATENAME(month, OrderDate), MONTH(OrderDate) ORDER BY MONTH(OrderDate)

/* MonthName SalesPerMonth

January 2483

February 2686

March 2750

April 2740.... */



-- Getting month name from month number

SELECT DATENAME(MM,DATEADD(MM,7,-1)) -- July

------------

------------

-- Extract string date from text with PATINDEX pattern matching

-- Apply sql server string to date conversion

------------

USE tempdb;

go

CREATE TABLE InsiderTransaction (

InsiderTransactionID int identity primary key,

TradeDate datetime,

TradeMsg varchar(256),

ModifiedDate datetime default (getdate()))

go



-- Populate table with dummy data

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Sell 5,648 8.49')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Option Execute 5,648 2.15')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Hammer, Bruce D. CSO 07-31-08 Buy 5,000 8.05')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Lennot, Mark B. Director 08-31-07 Buy 1,500 9.97')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC O''Neal, Linda COO 08-01-08 Sell 5,000 6.50')

go



-- Extract dates from stock trade message text

-- Pattern match for MM-DD-YY using the PATINDEX string function

SELECT TradeDate=substring(TradeMsg,

patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8)

FROM InsiderTransaction

WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0

/* Partial results

TradeDate

09-02-08

08-25-08

08-20-08 */



-- Update table with extracted date

-- Convert string date to datetime

UPDATE InsiderTransaction

SET TradeDate = convert(datetime, substring(TradeMsg,

patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8))

WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0



SELECT * FROM InsiderTransaction ORDER BY TradeDate desc

/* Partial results

InsiderTransactionID TradeDate TradeMsg ModifiedDate

1 2008-09-02 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10 2008-12-22 20:25:19.263

2 2008-08-25 00:00:00.000 INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70 2008-12-22 20:25:19.263

3 2008-08-20 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59 2008-12-22 20:25:19.263 */

-- Cleanup task

DROP TABLE InsiderTransaction



/************



VALID DATE RANGES FOR DATE / DATETIME DATA TYPES



DATE (3 bytes) date range:

January 1, 1 A.D. through December 31, 9999 A.D.



SMALLDATETIME (4 bytes) date range:

January 1, 1900 through June 6, 2079



DATETIME (8 bytes) date range:

January 1, 1753 through December 31, 9999



DATETIME2 (6-8 bytes) date range:

January 1, 1 A.D. through December 31, 9999 A.D.



-- The statement below will give a date range error

SELECT CONVERT(smalldatetime, '2110-01-01')

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a smalldatetime data type

resulted in an out-of-range value. */

************/

------------

-- SQL CONVERT DATE/DATETIME script applying table variable

------------

-- SQL Server convert date

-- Datetime column is converted into date only string column

DECLARE @sqlConvertDate TABLE ( DatetimeColumn datetime,

DateColumn char(10));

INSERT @sqlConvertDate (DatetimeColumn) SELECT GETDATE()



UPDATE @sqlConvertDate

SET DateColumn = CONVERT(char(10), DatetimeColumn, 111)

SELECT * FROM @sqlConvertDate



-- SQL Server convert datetime - String date column is converted into datetime column

UPDATE @sqlConvertDate

SET DatetimeColumn = CONVERT(Datetime, DateColumn, 111)

SELECT * FROM @sqlConvertDate



-- Equivalent formulation - SQL Server cast datetime

UPDATE @sqlConvertDate

SET DatetimeColumn = CAST(DateColumn AS datetime)

SELECT * FROM @sqlConvertDate

/* First results

DatetimeColumn DateColumn

2012-12-25 15:54:10.363 2012/12/25 */



/* Second results:

DatetimeColumn DateColumn

2012-12-25 00:00:00.000 2012/12/25 */

------------

-- SQL date sequence generation with DATEADD & table variable

-- SQL Server cast datetime to string - SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

WHILE ( @i < 500)

BEGIN

INSERT @Sequence DEFAULT VALUES

SET @i = @i + 1

END

SELECT DateSequence = CAST(DATEADD(day, Sequence,getdate()) AS varchar) FROM @Sequence

/* Partial results:

DateSequence

Dec 31 2008 3:02AM

Jan 1 2009 3:02AM

Jan 2 2009 3:02AM

Jan 3 2009 3:02AM

Jan 4 2009 3:02AM

Jan 5 2009 3:02AM */



------------

-- SQL Last Week calculations

------------

-- SQL last Friday

-- Implied string to datetime conversions in DATEADD & DATEDIFF

DECLARE @BaseFriday CHAR(8), @LastFriday datetime, @LastMonday datetime

SET @BaseFriday = '19000105'

SELECT @LastFriday = DATEADD(dd,

(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7, @BaseFriday)

SELECT [Last Friday] = @LastFriday

-- Result: 2008-12-26 00:00:00.000



-- SQL last Monday (last week's Monday)

SELECT @LastMonday=DATEADD(dd,

(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7 - 4, @BaseFriday)

SELECT [Last Monday]= @LastMonday

-- Result: 2008-12-22 00:00:00.000



-- SQL last week - SUN - SAT

SELECT [Last Week] = CONVERT(varchar,dateadd(day, -1, @LastMonday), 101)+ ' - ' +

CONVERT(varchar,dateadd(day, 1, @LastFriday), 101)

-- Result: 12/21/2008 - 12/27/2008



-----------------

-- Specific day calculations

------------

-- First day of current month

SELECT dateadd(month, datediff(month, 0, getdate()), 0)

-- 15th day of current month

SELECT dateadd(day,14,dateadd(month,datediff(month,0,getdate()),0))

-- First Monday of current month

SELECT dateadd(day, (9-datepart(weekday,

dateadd(month, datediff(month, 0, getdate()), 0)))%7,

dateadd(month, datediff(month, 0, getdate()), 0))

-- Last Friday of current month

SELECT dateadd(day, -7+(6-datepart(weekday,

dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,

dateadd(month, datediff(month, 0, getdate())+1, 0))

-- First day of next month

SELECT dateadd(month, datediff(month, 0, getdate())+1, 0)

-- 15th of next month

SELECT dateadd(day,14, dateadd(month, datediff(month, 0, getdate())+1, 0))

-- First Monday of next month

SELECT dateadd(day, (9-datepart(weekday,

dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,

dateadd(month, datediff(month, 0, getdate())+1, 0))



------------

-- SQL Last Date calculations

------------

-- Last day of prior month - Last day of previous month

SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)),101)

-- 01/31/2019

-- Last day of current month

SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0)),101)

-- 02/28/2019

-- Last day of prior quarter - Last day of previous quarter

SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate() ), 0)),101)

-- 12/31/2018

-- Last day of current quarter - Last day of current quarter

SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate())+1, 0)),101)

-- 03/31/2019

-- Last day of prior year - Last day of previous year

SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)),101)

-- 12/31/2018

-- Last day of current year

SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)),101)

-- 12/31/2019

------------

------------

-- SQL Server dateformat and language setting

------------

-- T-SQL set language - String to date conversion

SET LANGUAGE us_english

SELECT CAST('2018-03-15' AS datetime)

-- 2018-03-15 00:00:00.000



SET LANGUAGE british

SELECT CAST('2018-03-15' AS datetime)

/* Msg 242, Level 16, State 3, Line 2

The conversion of a varchar data type to a datetime data type resulted in

an out-of-range value.

*/

SELECT CAST('2018-15-03' AS datetime)

-- 2018-03-15 00:00:00.000



SET LANGUAGE us_english



-- SQL dateformat with language dependency

SELECT name, alias, dateformat

FROM sys.syslanguages

WHERE langid in (0,1,2,4,5,6,7,10,11,13,23,31)

GO

/*

name alias dateformat

us_english English mdy

Deutsch German dmy

Français French dmy

Dansk Danish dmy

Español Spanish dmy

Italiano Italian dmy

Nederlands Dutch dmy

Suomi Finnish dmy

Svenska Swedish ymd

magyar Hungarian ymd

British British English dmy

Arabic Arabic dmy

*/

------------

17 September 2010

To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

Correlated sub queries are more generalized to find n'th highest or nth lowest salary.

To Find Maximum Salary

SELECT * FROM `student` WHERE mark=(select max(mark) from student)

n means 1 st, 2nd, 3rd highest or lowest salary dont give n values,Give n-1 values
For eg
please give

(if you want 2nd highest salary give n=1
for 3rd n=2 like that.)

To find nth highest salary


SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

select top 2 * from Trainee where Salary in(select top 1 Salary from Depart)order by Salary DESC

To find 2nd lowest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

To find nth lowest salary

SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

15 September 2010

10 September 2010

Coding to get nth record in sql server

Coding to get nth record in sql server


for 10 th record

select top 1 * from (select top 10 * from dEmployeenew order by 1 desc)ac

choose 10 with nth

Option Explicit statement and Option Strict Statement

Option Explicit statement and Option Strict Statement

Option Explicit Statement

The Option Explicit has two modes. On and Off mode.

If Option Explicit mode in ON , you have to declare all the variable before you use it in the program . If not , it will generate a compile-time error whenever a variable that has not been declared is encountered .If the Option Explicit mode is OFF , Vb.Net automatically create a variable whenever it sees a variable without proper declaration.
By default the Option Explicit is On

Option Strict Statement
Visual Basic allows conversions of many data types to other data types. Data loss can occur when the value of one data type is converted to a data type with less precision or smaller capacity. A run-time error occurs if such a narrowing conversion fails. Option Strict ensures compile-time notification of these narrowing conversions so they can be avoided.
On
Optional. Enables Option Strict checking.
Off
Optional. Disables Option Strict checking. If On or Off is not specified, the default is Off.

Difference Between Int32.Parse(), Convert.ToInt32(), and Int32.TryParse()

Difference Between Int32.Parse(), Convert.ToInt32(), and Int32.TryParse()


Int32.parse(string)
Int32.Parse (string s)
method converts the string representation of a number to its 32-bit signed integer equivalent. When s is a null reference, it will throw ArgumentNullException. If s is other than integer value, it will throw FormatException. When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException. For example:

string s1 = "1234";
string s2 = "1234.65";
string s3 = null;
string s4 = "123456789123456789123456789123456789123456789";

int result;
bool success;

result = Int32.Parse(s1); //-- 1234
result = Int32.Parse(s2); //-- FormatException
result = Int32.Parse(s3); //-- ArgumentNullException
result = Int32.Parse(s4); //-- OverflowException

Convert.ToInt32(string)

Convert.ToInt32(string s) method converts the specified string representation of 32-bit signed integer equivalent. This calls in turn Int32.Parse () method. When s is a null reference, it will return 0 rather than throw ArgumentNullException. If s is other than integer value, it will throw FormatException. When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException.

For example:
result = Convert.ToInt32(s1); //-- 1234
result = Convert.ToInt32(s2); //-- FormatException
result = Convert.ToInt32(s3); //-- 0
result = Convert.ToInt32(s4); //-- OverflowException

Int32.Parse(string, out int) method converts the specified string representation of 32-bit signed integer equivalent to out variable, and returns true if it is parsed successfully, false otherwise. This method is available in C# 2.0. When s is a null reference, it will return 0 rather than throw ArgumentNullException. If s is other than an integer value, the out variable will have 0 rather than FormatException. When s represents a number less than MinValue or greater than MaxValue, the out variable will have 0 rather than OverflowException. For example:

success = Int32.TryParse(s1, out result); //-- success => true; result => 1234
success = Int32.TryParse(s2, out result); //-- success => false; result => 0
success = Int32.TryParse(s3, out result); //-- success => false; result => 0
success = Int32.TryParse(s4, out result); //-- success => false; result => 0
Convert.ToInt32 is better than Int32.Parse since it returns 0 rather than an exception. But again, according to the requirement, this can be used. TryParse will be the best since it always handles exceptions by itself.

9 September 2010

Java script validations

Java script validations
<--script type ="text/javascript">

//Validating the user inpus in the form
function Validate() {
if (document.getElementById("<%=txtName.ClientID%>").value == "")
{
alert("Name is a mandatory field and it can not be empty");
document.getElementById("<%=txtName.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=txtAddress.ClientID%>").value == "")
{
alert("Address is a Mandatory field and it can not be empty");
document.getElementById("<%=txtAddress.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=txtLandMark.ClientID%>").value == "")
{
alert("LandMark is Mandatory field and it can not be empty");
document.getElementById("<%=txtLandMark.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=txtDOB.ClientID%>").value == "")
{
alert("Date of Birth is a Mandatory field and it can not be empty");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;
}
else if (document.getElementById("<%=ddlTitle.ClientID%>").value == "-select-")
{
alert("please select the title field");
document.getElementById("<%=ddlTitle.ClientID%>").focus();
return false;
}

}

/* address validation function */
function addressCheck()
{

var msg = " Address field can accept only \n alpha numerics \n white spaces and the following special characters \n";
msg = msg+ " . / # - ,";
var a = 1;
var iChars = "~`!@$%^&*()+=[]\';{}|\":<>?";
var txtval = '';
var strField = document.getElementById("<%=txtAddress.ClientID%>").value;
for (var i = 0; i < strField.length; i++)
{
if (iChars.indexOf(strField.charAt(i)) != -1)
{
a = 1;
break;
}
else
a = 2;
}

if (a == 1)
{
alert(msg);
document.getElementById("<%=txtAddress.ClientID%>").focus();
return false;
}
else if (a == 2)
{
return true;
}

}

/* land mark validation function */
function landMarkCheck()
{
var msg = " LandMark field can accept only \n alpha numerics \n white spaces and the following special characters \n";
msg = msg + " . ,";
var a = 1;
var iChars = "~`!#@$%^&*()+=-[]\\\';/{}|\":<>?";
var txtval = '';
var strField = document.getElementById("<%=txtLandMark.ClientID%>").value;
for (var i = 0; i < strField.length; i++) {
if (iChars.indexOf(strField.charAt(i)) != -1)
{
a = 1;
break;
}
else
a = 2;
}

if (a == 1)
{
alert(msg);
document.getElementById("<%=txtLandMark.ClientID%>").focus();
return false;
}
else if (a == 2) {
return true;
}
}
/* date validation */
function dateCheck() {
var s = document.getElementById("<%=txtDOB.ClientID%>").value;
var y = s.substring(6, 10);
var d = s.substring(0, 2);
var m = s.substring(3, 5);
var cdate = new Date();
var yy = cdate.getFullYear() - 1;
//checking the date is between 1910 to current date
if ((y >= 1900) && (y <= yy))
{
return true;
}
else if(y==yy+1)
{
if (m <= cdate.getMonth() + 1)
{
if (m == cdate.getMonth() + 1)
{
if (d <= cdate.getDate())
{
if (d == cdate.getDate());
return true;
}
else {
alert("Date of birth should be from 1910 to current date and also in the specified format ");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;
}
}

}
else {
alert("Date of birth should be from 1910 to current date and also in the specified format");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;

}
}
else {
alert("Date of birth should be from 1910 to current date and also in the specified format");
document.getElementById("<%=txtDOB.ClientID%>").focus();
return false;
}


}

<--script type="text/javascript">




1. write scripts with in tag
2. call the functions from code behind using script registration.

example:

page scripts of : add or edit page



----------------------------------------------------------------------------------------------------------

script registration in code behind for add or edit page

protected void Page_Load(object sender, EventArgs e)
{
//checking page is
if (!Page.IsPostBack)
{
//here we can add the jscript functions to the required control's events
btnSubmit.Attributes.Add("OnClick", "return Validate();");
txtAddress.Attributes.Add("onblur", "return addressCheck();");
txtLandMark.Attributes.Add("onblur", "return landMarkCheck();");
txtDOB.Attributes.Add("onblur", "return dateCheck();");
}
}

---------------------------------------------------------------------------------------------------------
page script for : modify page



---------------------------------------------------------------------------------------------------------

script registration in code behind for modify page


if (!Page.IsPostBack)
{
if (gvTestDetails.Rows.Count != 0)
{ btnDelete.Attributes.Add("OnClick", "return pop();"); }
else
{
btnDelete.Attributes.Add("OnClick", "return pop1();");
btnEdit.Attributes.Add("OnClick", "return pop2();");

}
}

E-mail Validation Using REGEX

E-mail Validation Using REGEX

string email = txtEmail.Text.ToString();
string pattern = @"^[a-z][a-z|0-9|]*([_][a-z|0-9]+)*([.][a-z|" + @"0-9]+([_][a-z|0-9]+)*)?@[a-z][a-z|0-9|]*\.([a-z]" + @"[a-z|0-9]*(\.[a-z][a-z|0-9]*)?)$";
System.Text.RegularExpressions.Match match = Regex.Match(email, pattern, RegexOptions.IgnoreCase);
if (match.Success)
{
}
Else
{
{
MessageBox.Show("Don't Leave the Field Empty", "Name Entry Error",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}

VB.NET VALIDATION USING ASCII

VB.NET VALIDATION USING ASCII


For Numeric

Public Function OnlyNumeric(ByVal Key As String) As Boolean
If (Key >= 48 And Key <= 57) Or Key = 8 Then
OnlyNumeric = False
Else
OnlyNumeric = True
End If
End Function
--------------------------------------------------------------------------------------------


For Character

Public Function OnlyCharacter(ByVal key As String) As Boolean
If (key >= 65 And key <= 90) Or (key >= 97 And key <= 122) Or key = 8 Then
OnlyCharacter = False

Else
OnlyCharacter = True
MsgBox("Enter only characters")
End If
End Function


-----------------------------------------------------------------------------------------------


create this function and than call this function into that particular "textbox"(KeyPress event) like this way


Private Sub txtcitycode_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
e.Handled = OnlyNumeric(Asc(e.KeyChar))
End Sub

Private Sub txtcityname_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtcityname.KeyPress
e.Handled = OnlyCharacter(Asc(e.KeyChar))
End Sub

C# VALIDATION USING ASCII

C# VALIDATION USING ASCII

//for ONLY NUMBERS

private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
{

if ((e.KeyChar >= 48 && e.KeyChar <= 57 || e.KeyChar == 8))
{
e.Handled = false;
}
else
{
e.Handled = true;
MessageBox.Show("Please Enter a Valid Number", "Number Validation",
MessageBoxButtons.OK);
}

}
-----------------------------------------------------------------------------------------------
//for ONLY CHARCTERS

private void textBox2_KeyPress(object sender, KeyPressEventArgs e)
{
if ((e.KeyChar >= 65 && e.KeyChar <= 90) || (e.KeyChar >= 97 && e.KeyChar<=122) || e.KeyChar==8)
{
e.Handled = false;
}
else
{
e.Handled = true;
MessageBox.Show("Please Enter a Character", "Number Validation",
MessageBoxButtons.OK);
}
}

Query Getting the Maximum salary of the employee by department wise SUBQUERY Example

Query Getting the Maximum salary of the employee by department wise

SUBQUERY Example


select
E.fname +' '+ E.lname as EmpName
,T.Salary
,D.depname
,D.depid
from
Employenew E,
departnew D
,(select max(salary) as Salary ,depid from Employenew group by depid) as T
where
E.depid=D.depid
and T.depid=D.depid
and E.Salary =T.Salary

To find Second highest salary

To find Second highest salary

select top 2 * from Trainee.dbo.Depart where Salary in(select top 1 Salary from Trainee.dbo.Depart)order by Salary DESC

Coding to get nth record in sql server 10 th record

Coding to get nth record in sql server 10 th record

select top 1 * from (select top 10 * from dEmployeenew order by 1 desc)ac

Trigger in Sql

Create Trigger For Insert
ALTER trigger [dbo].[trinsert]
on [dbo].[emp]

after insert
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from inserted)
set @depid=(select depid from inserted)
set @fname=(select fname from inserted)
set @lname=(select lname from inserted)
set @gender=(select gender from inserted)
set @dob=(select dob from inserted)
set @doj=(select doj from inserted)
set @email=(select email from inserted)
set @city=(select city from inserted)
set @astatus='inserted'
set @Dateandtime=getdate()
set @salary=(select salary from inserted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)


Trigger For After Delete

ALTER trigger [dbo].[trdelete]
on [dbo].[emp]

after delete
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from deleted)
set @depid=(select depid from deleted)
set @fname=(select fname from deleted)
set @lname=(select lname from deleted)
set @gender=(select gender from deleted)
set @dob=(select dob from deleted)
set @doj=(select doj from deleted)
set @email=(select email from deleted)
set @city=(select city from deleted)
set @astatus='deleted'
set @Dateandtime=getdate()
set @salary=(select salary from deleted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)

Trigger For Update
ALTER trigger [dbo].[trupdate]
on [dbo].[emp]

after update
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from inserted)
set @depid=(select depid from inserted)
set @fname=(select fname from inserted)
set @lname=(select lname from inserted)
set @gender=(select gender from inserted)
set @dob=(select dob from inserted)
set @doj=(select doj from inserted)
set @email=(select email from inserted)
set @city=(select city from inserted)
set @astatus='updated'
set @Dateandtime=getdate()
set @salary=(select salary from inserted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)

Stored Procedure For reference

Stored Procedure For reference


CREATE PROCEDURE sp_Insertion1
(@sempId int,@sempname varchar(50),@sgender varchar(50),@sstreet varchar(50),@semail varchar(50),@spincode varchar(50))
as
insert into employeear(fempid,fempname,fgender,fstreet,femail,fpincode)
values
(@sempId,@sempname,@sgender,@sstreet,@semail,@spincode)


private void btnNew_Click(object sender, EventArgs e)
{

string empid,empname, gender, street, email, pincode;
empid = txtEmpId.Text;
empname = txtEmpName.Text;
gender = comboGender.SelectedItem.ToString();
street = rtStreet.Text;
email = txtEmail.Text;
pincode = txtPincode.Text;
int empidint = int.Parse(empid);
SqlCommand dbcmd = new SqlCommand();
SqlConnection conn = new SqlConnection("Server=192.168.1.5;" + "Database=trainee;"+ "User ID=senthilkumar;" + "Password=gtpl;" + "Trusted_Connection=false;");
SqlCommand command = new SqlCommand("sp_Insertion1", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@sempid", SqlDbType.Int).Value = empidint;
command.Parameters.Add("@sempname",SqlDbType.VarChar).Value = empname;
command.Parameters.Add("@sgender", SqlDbType.VarChar).Value = gender;
command.Parameters.Add("@sstreet", SqlDbType.VarChar).Value = street;
command.Parameters.Add("@semail", SqlDbType.VarChar).Value = email;
command.Parameters.Add("@spincode",SqlDbType.VarChar).Value = pincode;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();
}
Update Stored Procedure
ALTER PROCEDURE sp_updation(@sempId int
,@sempname varchar(50)
,@sgender varchar(50)
,@sstreet varchar(50)
,@semail varchar(50)
,@spincode varchar(50))
as
update employeear

set fempid=@sempId
,fempname=@sempname
,fgender=@sgender
,fstreet=@sstreet
,femail=@semail
,fpincode=@spincode
where fempid=@sempId



Delete data from stored procedure



create procedure sp_address_delete(@sid int)as
delete from address where tid=@sid



Select data from Stored Procedure



create procedure sp_address_show(@sid int)
as
select * from address where tid=@sid



Select stored procedure with data type conversion

ALTER procedure [dbo].[sp_address_show](@sid int)
as
select
ttitle,
tname,
taddress,
tlandmark,
tresident,CONVERT (varchar(10),tdob) from address where tid=@sid




Update the table with type conversion


create procedure sp_address_update
(
@sid int
,@stitle varchar(10)
,@sname varchar(25)
,@saddress varchar(55)
,@slandmark varchar(25)
,@sresident varchar(10)
,@sdob varchar(10)
)
as begin
update address
set
ttitle=@stitle,
tname=@sname,
taddress=@saddress,
tlandmark=@slandmark,
tresident=@sresident,
tdob=CONVERT(datetime,@sdob,103)
where
tid=@sid

end


Stored Procedure for Insertion


CREATE PROCEDURE sp_Insertion1
(@sempId int,@sempname varchar(50),@sgender varchar(50),@sstreet varchar(50),@semail varchar(50),@spincode varchar(50))
as
SET NOCOUNT ON;
insert into employeear(fempid,fempname,fgender,fstreet,femail,fpincode)
values
(@sempId,@sempname,@sgender,@sstreet,@semail,@spincode)
Multiple Queries in stored procedure
alter procedure sp_insertnew(

@empid int,
@depid int,
@depname varchar(20),
@fname varchar(20),
@lname varchar(20),
@gender varchar(6),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@dateandtime datetime)
as begin

insert into Employenew(
empid,depid,fname,lname,gender,dob,doj,email,city)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city)

insert into hEmployeenew(
empid,depid,fname,lname,gender,dob,doj,email,city,astatus,dateandtime)
values(
@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@dateandtime)

insert into Departnew(
depid,depname)
values(
@depid,@depname)
end

Queries For reference

Queries For Reference

Creating Table Employee


create table Employe
(Empid int primary key identity,
Empname varchar(20),
Dob datetime,
Doj datetime,
sex varchar(10))

Creating table Depart

create table Depart(
Depid int not null,
Empid int not null,
Dept varchar(15),
Salary int)

Alter Primary Key

alter table Departnew add primary key(depid)


select * from Employe
select * from Employe where Empid=2
select * from Employe where Empid!=2
select Empid,Empname from Employ

Top Queries

Select Top 1 * from Employe
select Top 25 percent * from Employe
select top 25 percent Empid,Empname from Employe
select top 25 percent * from Employe where Empid=1

Using Top query in Descending Order
select top 25 percent * from Employe order by Empid Desc

Distinct Queries

select distinct Empname from Employe
select COUNT(distinct Empname)from Employe

Query Multiple Tables

select Employe.Empname,Employe.Dob,Depart.Depid,Depart.Salary from Employe,Depart where Employe.Empid=Depart.Depid

Sub Queries

select Empid,Empname from Employe where exists(Select Empid from Depart where Employe.Empid=Depart.Empid)

select Depid,Empid,Dept,Salary from Depart where Salary in(Select MAX(Salary)from Depart)


Orderby

select * from Trainee.dbo.Depart order by Empid desc

Aggregate Function

select AVG(Salary)"Average salary"from Trainee.dbo.Depart

select * from Trainee.dbo.Depart where Salary=(select MIN(Salary)from Trainee.Dbo.Depart)

select * from Trainee.dbo.Depart where Salary=(select MAX(Salary)from Trainee.Dbo.Depart)



To create databackup in table without making schema

select * into Worker1 from Employe

Union
select * from Worker1 union Select * from Employe

Union all
select * from Worker1 unionall Select * from Employe

Inner Join

select * from Employe join Depart on Employe.Empid=Depart.Empid

Left Join

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
select * from Employe left join Depart on Employe.Empid=Depart.Empid

Right Join

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
select * from Employe Right join Depart on Employe.Empid=Depart.Empid


FULL JOIN


FULL JOIN: Return rows when there is a match in one of the tables

select * from Employe FULL JOIN Depart on Employe.Empid=Depart.Empid

SQL Constraints

Constraints are used to limit the type of data that can go into a table.
NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,DEFAULT
SQL DEFAULT Constraint
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

Group By function

select Depid,sum(Salary) from Depart group by Depid


Count Function


select COUNT (*) from Employe

Count Disinct Column

SELECT COUNT(DISTINCT Dept) FROM Depart

To find Length

SELECT len(Empname) from Employe

EXCEPT operator
SELECT * from Employe where Empid between 1 AND 8
EXCEPT
SELECT * from Employe where Empid between 5 and 8

INTERSECT operator

The SQL INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets
SELECT * from Employe where Empid between 1 AND 8
intersect
SELECT * from Employe where Empid between 5 and 8
Using the % Wildcard and like operator
select * from Employe where Empname like 'a%'

Dateadd

SELECT Empid,DATEADD(day,45,Doj) AS Doj
FROM Employe
Datediff
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='1984-02-29'
SET @EndDate ='2008-02-29'
SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Quarter Difference

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'
SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate

Month Difference
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'
SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Datetime format
Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06


SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007


SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

How To Link Within A Page.

How To Link Within A Page.

Do you want to people to be able to get to different parts of your page easier or do you just want to add a link so people can get back to the top of the page easily? Then come in and find out how.

Here's How:

1.Create the hook for where you want to go when the main link is clicked on: Title Of The Page
2.Give your link a name (here it is called top) and give it a title.
3.Create the link that will be clicked on. This link tells the browser where to go: Back To The Top
4.Use the same name as used in the hook and give it a title (here the title is Back To The Top).

Tips:

1.Make sure you give the same name to each or it won't work!

8 September 2010

How Internet Infrastructure Works

How Internet Infrastructure Works

One of the greatest things about the Internet is that nobody really owns it. It is a global collection of networks, both big and small. These networks connect together in many different ways to form the single entity that we know as the Internet. In fact, the very name comes from this idea of interconnected networks.
Since its beginning in 1969, the Internet has grown from four host computer systems to tens of millions. However, just because nobody owns the Internet, it doesn't mean it is not monitored and maintained in different ways. The Internet Society, a non-profit group established in 1992, oversees the formation of the policies and protocols that define how we use and interact with the Internet.
In this article, you will learn about the basic underlying structure of the Internet. You will learn about domain name servers, network access points and backbones. But first you will learn about how your computer connects to others.


The Internet: Computer Network Hierarchy


Every computer that is connected to the Internet is part of a network, even the one in your home. For example, you may use a modem and dial a local number to connect to an Internet Service Provider (ISP). At work, you may be part of a local area network (LAN), but you most likely still connect to the Internet using an ISP that your company has contracted with. When you connect to your ISP, you become part of their network. The ISP may then connect to a larger network and become part of their network. The Internet is simply a network of networks.
Most large communications companies have their own dedicated backbones connecting various regions. In each region, the company has a Point of Presence (POP). The POP is a place for local users to access the company's network, often through a local phone number or dedicated line. The amazing thing here is that there is no overall controlling network. Instead, there are several high-level networks connecting to each other through Network Access Points or NAPs.

Internet Network Example


Heres an example.

Imagine that Company A is a large ISP. In each major city, Company A has a POP. The POP in each city is a rack full of modems that the ISP's customers dial into. Company A leases fiber optic lines from the phone company to connect the POPs together (see, for example, this UUNET Data Center Connectivity Map).
Imagine that Company B is a corporate ISP. Company B builds large buildings in major cities and corporations locate their Internet server machines in these buildings. Company B is such a large company that it runs its own fiber optic lines between its buildings so that they are all interconnected.
In this arrangement, all of Company A's customers can talk to each other, and all of Company B's customers can talk to each other, but there is no way for Company A's customers and Company B's customers to intercommunicate. Therefore, Company A and Company B both agree to connect to NAPs in various cities, and traffic between the two companies flows between the networks at the NAPs.
In the real Internet, dozens of large Internet providers interconnect at NAPs in various cities, and trillions of bytes of data flow between the individual networks at these points. The Internet is a collection of huge corporate networks that agree to all intercommunicate with each other at the NAPs. In this way, every computer on the Internet connects to every other.
All of these networks rely on NAPs, backbones and routers to talk to each other. What is incredible about this process is that a message can leave one computer and travel halfway across the world through several different networks and arrive at another computer in a fraction of a second!
The routers determine where to send information from one computer to another. Routers are specialized computers that send your messages and those of every other Internet user speeding to their destinations along thousands of pathways. A router has two separate, but related, jobs:
• It ensures that information doesn't go where it's not needed. This is crucial for keeping large volumes of data from clogging the connections of "innocent bystanders."
• It makes sure that information does make it to the intended destination.
In performing these two jobs, a router is extremely useful in dealing with two separate computer networks. It joins the two networks, passing information from one to the other. It also protects the networks from one another, preventing the traffic on one from unnecessarily spilling over to the other. Regardless of how many networks are attached, the basic operation and function of the router remains the same. Since the Internet is one huge network made up of tens of thousands of smaller networks, its use of routers is an absolute necessity. For more information,

Internet Backbone

The National Science Foundation (NSF) created the first high-speed backbone in 1987. Called NSFNET, it was a T1 line that connected 170 smaller networks together and operated at 1.544 Mbps (million bits per second). IBM, MCI and Merit worked with NSF to create the backbone and developed a T3 (45 Mbps) backbone the following year.
Backbones are typically fiber optic trunk lines. The trunk line has multiple fiber optic cables combined together to increase the capacity. Fiber optic cables are designated OC for optical carrier, such as OC-3, OC-12 or OC-48. An OC-3 line is capable of transmitting 155 Mbps while an OC-48 can transmit 2,488 Mbps (2.488 Gbps). Compare that to a typical 56K modem transmitting 56,000 bps and you see just how fast a modern backbone is.
Today there are many companies that operate their own high-capacity backbones, and all of them interconnect at various NAPs around the world. In this way, everyone on the Internet, no matter where they are and what company they use, is able to talk to everyone else on the planet. The entire Internet is a gigantic, sprawling agreement between companies to intercommunicate freely.
Internet Protocol: IP Addresses
Every machine on the Internet has a unique identifying number, called an IP Address. The IP stands for Internet Protocol, which is the language that computers use to communicate over the Internet. A protocol is the pre-defined way that someone who wants to use a service talks with that service. The "someone" could be a person, but more often it is a computer program like a Web browser.
A typical IP address looks like this:
216.27.61.137
To make it easier for us humans to remember, IP addresses are normally expressed in decimal format as a dotted decimal number like the one above. But computers communicate in binary form. Look at the same IP address in binary:
11011000.00011011.00111101.10001001
The four numbers in an IP address are called octets, because they each have eight positions when viewed in binary form. If you add all the positions together, you get 32, which is why IP addresses are considered 32-bit numbers. Since each of the eight positions can have two different states (1 or zero), the total number of possible combinations per octet is 28 or 256. So each octet can contain any value between zero and 255. Combine the four octets and you get 232 or a possible 4,294,967,296 unique values!
Out of the almost 4.3 billion possible combinations, certain values are restricted from use as typical IP addresses. For example, the IP address 0.0.0.0 is reserved for the default network and the address 255.255.255.255 is used for broadcasts

Consistency level in Azure cosmos db

 Consistency level in Azure cosmos db Azure Cosmos DB offers five well-defined consistency levels to provide developers with the flexibility...