Time taken by a normal broadband service to download a file in MB/GB

Many of us have questioned as

 “Which broadband plan is better for me?”

“Which plans to choose for internet connection?”

“What should be check before buying an internet / broadband connection?”

“Should I buy an unlimited bandwidth plan or limited bandwidth plan?”

“How to calculate data downloaded by me from internet/broadband connection”

“How much we can download on my Broadband connections per hour”

This post will finally help you to understand broadband concept and help you in buying the best broadband / internet plan for you…

Here I will explain how much data you can download on different Broadband speed.  It all depends on broadband speed you opt for. 

This also helps us in limiting our usage, if we are using Limited bandwidth connections. (Like 5 Mbps till 20 GB then speed limits to 512Kbps or so on)

Please note: Every broadband connections do not provide continuous / fixed speed all period of time of connection, they vary time to time, thus transfer rate (at which data file is downloaded) also varies.

Thus it is not guaranteed that below speed is what I MUST get; it depends on the fluctuations on speed/ transfer rate of internet connection….

Following table will help you to understand which broadband plans will satisfy your needs.

Broadband Speed / Connections	Transfer rate	Max download with Full speed In 1 hour
128 kbps	16 KBps	56.25 MB
256 kbps	32 KBps	112.5 MB
512 kbps	64 KBps	225 MB
784 kbps	98 KBps	344.53125 MB
1 Mbps	128 KBps	450 MB
2 Mbps	256 KBps	900 MB
4 Mbps	512 KBps	1800 MB (1.75GB)
8 Mbps	1024 KBps (1 MBps)	3600  MB (3.51GB)
16 Mbps	2048 KBps (2 MBps)	7200 MB (7.03GB)
32 Mbps	4096 KBps (4 MBps)	14400 MB (14.06 GB)

Thus if you have a broadband connection as 1 Mbps, you will be able to download 450 MB Max to max in one hour.

For detailed chart of Download Per hr Click here 

 For difference between transfer rate and download speed please click here

IIS Port numbers?

80 is the default port for HTTP traffic
21 is the default port for FTP for sending and recieving
data.
443 is the defaulr for SSL traffic
port 25 smtp



FTP: 20, 21,
SMTP: 28,
HTTP: 80,
SSL:443,
DNS: 53
POP3:110
SMTP:25
NNTP:119
IMAP:142


SSL port no:

HTTP: 443
SMTP:456
POP3:995
IMAP:993
NNTP:563
LDAP:636

Updating a set of rows in Sql Server : How to use While loop in Sql Server

Declare @PubId int
set @PubId = 1046

Declare @NewsId int
set @NewsId = 7026


Begin Tran T2
WHILE (@NewsId <= 7125)
BEGIN


update MstReports
set ReportCode = 'Ovum' + CONVERT(VARCHAR(10),@PubId)
where NewsId = @NewsId


print @newsid

SET @NewsId = @NewsId + 1
SET @PubId = @PubId + 1
IF @NewsId = 7126
BREAK;
END

rollback

or

commit

How to do Exception handling (try catch block) in Sql Server 2005 and also maintain Transcation: Insert query in sql Sever 2005

Hello all,

This Article explains how to create a Stored procedure in sql server 2005 with Exception handling.
Old way was to use if else condition with @@error and raiserror Method
But the sql server 2005 has new way to handle Exception using try catch block...

Hey
this code is also important in case of maintaining Transaction In sql server.

if in a stored procedure there is more than one Insert queries , which insert the incoming data in more than one table then transaction has to be maintained.

If transaction is not maintained then Half of the information is inserted and half information is not inserted in database .

That is all the data is inserted in table whose insert quires arrive before the Query which throw exception and all insert quries below that error quiry is not fired and Transaction failed .
whole database structure and constaint failure.

in sql server transaction is maintained By
RollBack and Commit

Thus the New code in Sql server:

CraetePROCEDURE [dbo].[proc_LoginCreateUser]
(
@emailid nvarchar(max)
,@password nvarchar(max)
,@firstname nvarchar(100)
,@lastname nvarchar(100)
,@jobtitle nvarchar(max)
,@company nvarchar(max)
,@phoneno nvarchar(50)
,@intrests nvarchar(max)

)
AS

BEGIN TRY
BEGIN TRANSACTION -- Start the transaction

declare @CheckemailID nvarchar(max)

-- to get the Current password of that user by loginid(session)
SELECT @CheckemailID=Email_ID
FROM [Login_UserDetails]
where Email_ID = @emailid


IF (@CheckemailID <> @emailid or @CheckemailID is null )
BEGIN

--insert a new record in "Login_UserDetails" to create a new user
--note: Login Id is Auto incremented so no use to insert it...

insert into dbo.Login_UserDetails
(UserID
,Email_ID
,Password
,FirstName
,LastName
,JobTitle
,Company
,Phone
,Interests
,UserVisits
,CreatedDate
,ModifiedDate
,IsActive)
values(NEWID()
,@emailid
,@password
,@firstname
,@lastname
,@jobtitle
,@company
,@phoneno
,@intrests
,1
,getdate()
,getdate()
,null)

Select 'Right'

END
ELSE
BEGIN
Select 'Wrong'
END
-- This Means all the thing is fine and now commit this transaction
COMMIT
END TRY
BEGIN CATCH
-- There was a exception occured somewhere and please roll back
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH


This is the actual way insert / update query must be written in Sqlserver 2005

Get all record entered today or yesteday in sql server, Other Sql Functions of Conversions

Most Of the time we fiddle in Sql for performing the operations on Date format.
any other Data format is easy to manipulate like numeric, int etc on which we can perform operation
like (<,>,=,<=,,,,,)

But What when we have a requirement that show all the record which are entered on current date , on specified date, before a current date
etc.

take a example i want all record on today's date

We cannot use :
select * from dailyupdates where Date = getdate()

this will result in no record found....

We cannot perform any operation like : "like,in" etc...

This is because the date is in datetime format andwhere clause cjheck time as well as date


So we have to use convert funtions in Sql server.
By using convert function we convert the date in Varchar format and then substring(cut) the date format only
and then check the Date format

So result is :
This solution is to get all the record entered today
select * from Dailyreports where convert(varchar(10),Date, 101) = convert(varchar(10),getdate(), 101)


The solution for getting Yesterday's entered record are
select * from Dailyreports where convert(varchar(10),Date, 101) = convert(varchar(10),getdate()-1, 101)

Likely other functions are

-- to get today's Date
select getdate() -- 2009-11-27 16:58:40.413 (yyyy-mm-dd)

--To get Current month
select convert(varchar(2),getdate(), 101) --the result is 11 : month

--To get Current month and Date
select convert(varchar(5),getdate(), 101) --the result is 11/27 27th day of 11th Month

-- to get date in regular format
select convert(varchar(10),getdate(), 101) --the result is 11/27/2009 : Current date mm/dd/yyyy


---------


-- to get date in .(dot) format
select convert(varchar(10),getdate(), 102) --the result is 2009.11.27 (yyyy.mm.dd)

-- the regular way to show Dates
select convert(varchar(10),getdate(), 103) --the result is 27/11/2009 (dd/mm/yyyy)

-- Date in Dot(.) format
-- can use substring functions to gate dd mm and yyyy
select convert(varchar(10),getdate(), 104) --the result is 27.11.2009 (dd.mm.yyyy)

-- Date in Dash(-) format
select convert(varchar(10),getdate(), 105) --the result is 27-11-2009 (dd-mm-yyyy)

-- Abbrivated way of expressing Date
select convert(varchar(11),getdate(), 106) --the result is 27 Nov 2009 (dd month yyyy)

--New Abbr way of expressing Date
select convert(varchar(12),getdate(), 107) --the result is Nov 27, 2009 (month dd, yyyy)


-- get time ---
select convert(varchar(8),getdate(), 108) --the result is 17:05:17 hrs:min:sec

-- abbr way of display date
select convert(varchar(12),getdate(), 109) --the result is Nov 27 2009 (month dd yyyy)

-- way of displaying date
select convert(varchar(15),getdate(), 110) --the result is 11-27-2009 (mm-dd-yyyy)

-- way of displaying date
select convert(varchar(12),getdate(), 111) --the result is 2009/11/27 (yyyy/mm/dd)

-- way of displaying date 'Unique way'
select convert(varchar(12),getdate(), 112) --the result is 20091127

---- way of displaying date
select convert(varchar(25),getdate(), 113) --the result is 27 Nov 2009 17:08:53:500

-- get date hrs:min:sec:msec
select convert(varchar(14),getdate(), 114) --the result is 17:09:21:670
-------------------------

-- to get today's date
select getdate() --the result is 2009-11-27 17:16:41.093

-- to get Yesterday's date
select getdate()- 1 -- the result is 2009-11-26 17:16:49.233

-- to get all records irresrective of time (only check date)
select convert(varchar(10),getdate()-1, 101) -- the result is 11/26/2009

-- to get all reords of employee work from dailyreports of yesterday
select * from Dailyreports where convert(varchar(10),Date, 101) = convert(varchar(10),getdate()-1, 101)

List of SQL Server Functions like Cast(), Round() Ceiling() Floor()

Sql server has many inbuild function like sum(), count(),round(), cast() .....
examples of some are

select ((30.250250/80)*100) as Percentage result: 37.812812500

select round(((30.250250/80)*100),1) result: 37.800000000
select round(((30.250250/80)*100),2) result: 37.810000000
select round(((30.250250/80)*100),3) result: 37.812000000
select round(((30.250250/80)*100),4) result: 37.812800000
select round(((30.250250/80)*100),5) result: 37.812810000

select ROUND(748.58, -1) result: 750.00
select ROUND(748.58, -2) result: 700.00
select ROUND(748.58, 0) result: 749.00
select ROUND(748.58, 1) result: 748.60
select ROUND(748.58, 2) result: 748.58


SELECT ROUND(123.4567, -1); result: 120.0000
SELECT ROUND(123.4567, -2); result: 100.0000
SELECT ROUND(123.4567, -3); result: 0.0000
SELECT ROUND(123.4567, -4); result: 0.0000
SELECT ROUND(123.4567, -5); result: 0.0000


select Ceiling(((30.250250/80)*100)) result: 38
select Floor(((30.250250/80)*100)) result: 37

select CAST(((30.250250/80)*100) AS DECIMAL (9,0)) result: 38
select CAST(((30.250250/80)*100) AS DECIMAL (9,1)) result: 37.8
select CAST(((30.250250/80)*100) AS DECIMAL (9,2)) result: 37.81
select CAST(((30.250250/80)*100) AS DECIMAL (9,3)) result: 37.8121
select CAST(((30.250250/80)*100) AS DECIMAL (9,4)) result: 37.8128
select CAST(((30.250250/80)*100) AS DECIMAL (9,5)) result: 37.81281

Note: In this case DECIMAL (9,0) : 9 represent no of digit before decimal point
and 0,1,2,3,4... represent digit after decimal point

Restriction in Group By and Having clause oF Select Statement in sql server

In case of Reporting application of employee / Payroll system we have to calculate the no of hours worked by each employee in different department on different tasks.

To generate a report based on this data, we have to use aggregate function like sum()
in SQL server.
But for that we have to use �Group by� clause to make group of employee by (Employee_Id) and if required we also have to use �Having clause� with group by clause in select statement to restrict some group of record to display.

While using Group By clause there is restriction of using it.

The columns which are used in SELECT clause should appear in GROUP BY clause.
Except the column used in aggregate function.
That is all the column/field in select statement should be used in group by clause and it is must. But some of the fields like no_Of_Hours_worked, Sum_of_days etc should use aggregate function and it is not mandatory to put it in Group clause.


Example

SELECT Employee_ID,Department_Name,
Taskname,sum([HoursWorked])as Workered_hrs ,no_of_hrs as Std_hours
FROM [DailyUpdate] upd ,[Eployee_Master] rep, [Task_Master] task
WHERE upd.Report_ID= rep.ID and upd.Task_ID = task.TaskId
group by Department_Name,Taskname,Employee_ID,no_of_hrs
having Employee_ID = 16


Finally:

The columns which are used in SELECT clause should appear in GROUP BY clause except the column used in aggregate function.