Disk storage capacity

 

1 Bit = Binary Digit
8 Bits = 1 Byte
1024 Bytes = 1 Kilobyte
1024 Kilobytes = 1 Megabyte
1024 Megabytes = 1 Gigabyte
1024 Gigabytes = 1 Terabyte
1024 Terabytes = 1 Petabyte
1024 Petabytes = 1 Exabyte
1024 Exabytes = 1 Zettabyte
1024 Zettabytes = 1 Yottabyte
1024Yottabytes = 1 Brontobyte
1024 Brontobytes = 1 Geopbyte
1024 Geopbyte=1 Saganbyte
1024 Saganbyte=1 Pijabyte
1024 Pijabyte  = 1 Alphabyte
1024 Alphabyte = Kryatbyte
1024 Kryatbyte = Amosbyte  
1024 Amosbyte  = Pectrolbyte
1024 Pectrolbyte = Bolgerbyte
1024 Bolgerbyte = Sambobyte
1024 Sambobyte = Quesabyte
1024 Quesabyte = Kinsabyte
1024 Kinsabyte = Rutherbyte
1024 Rutherbyte = Dubnibyte
1024 Dubnibyte = Seaborgbyte
1024 Seaborgbyte = Bohrbyte
1024 Bohrbyte = Hassiubyte
1024 Hassiubyte = Meitnerbyte
1024 Meitnerbyte = Darmstadbyte
1024 Darmstadbyte = Roentbyte
1024 Roentbyte = Coperbyte

@@RowCount Vs RowCount_Big()

ROWCOUNT_BIG Function :

ROWCOUNT_BIG function returns number of rows affected by last executed statement. It works same as @@ROWCOUNT except the return type differs.  

This variable is set to 0 by any statement that does not return rows, such as an IF statement.

If the table has more than 2 billion rows, use ROWCOUNT_BIG().

returns a big integer value. If the number of rows to affect is in between 0 and 9,223,372,036,854,775,807

Return type of ROWCOUNT_BIG function is a bigint.

select orderNr from orp

select RowCount_Big()

@@RowCount :

@@ROWCOUNT returns an integer value. So, Use this statement if the number of rows to affect is in between 0 and 2,147,483,647.

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

• Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
• Preserve @@ROWCOUNT from the previous statement execution.
• Reset @@ROWCOUNT to 0 but do not return the value to the client.

1. Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT ‘Generic Text‘.
2. Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.
3. Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.
4. DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.
5. EXECUTE statements preserve the previous @@ROWCOUNT.
6. Statements such as USE, SET, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION

select orderNr from orp

select @@rowcount

SET Rowcount :

Set RowCount is used to restrict the  number of rows to process by the query processor.

Example:

select * from A1

Output:

Id

1

2

NULL

4

set rowcount 2 -- Set no of rows to process
select * from A1

Output:

id
1
2

set rowcount 0 -- Reset the row count value

ROW_NUMBER() in SQL SERVER

ROW_NUMBER() returns a column as an expression that contains the row’s number within the result set. This is only a number used in the context of the result set.

 if the result changes, the ROW_NUMBER() will change.

select distinct ROW_NUMBER()over (order by sal desc ) as [Row Number],EMPNO from EMP

      Distinct will not work when we use ROW_NUMBER

Row Number           EMPNO

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

1                    7839

2                    7902

3                    7788

4                    7566

5                    7698

6                    7782

7                    7499

8                    7844

9                    7934

10                   7521

11                   7654

12                   7876

13                   7900

14                   7369

15                   7370

16                   7370

(16 row(s) affected)

select distinct ROW_NUMBER()over (order by sal desc ) as [Row Number],EMPNO from EMP where EMPNO>7800

Row Number           EMPNO

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

1                    7839

2                    7902

3                    7844

4                    7934

5                    7876

6                    7900

(6 row(s) affected)

select ROW_NUMBER()over(PARTITION by EMPNO

order by sal desc )

 as [Row_number],EMPNO ,SAL

 from

      EMP

where EMPNO>7800

Row_number           EMPNO                                   SAL

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

1                    7839                                    5000.00

1                    7844                                    1500.00

1                    7876                                    1100.00

1                    7900                                    950.00

1                    7902                                    3000.00

1                    7934                                    1300.00

----------------------------------------------------*********----------------------------------------------------

--------------------------------------*****************Endof example1------------------------------------------------

Example 2:

create table MyPeople(PersonID int,FamilyID int,FName varchar(100),LName varchar(100),DOB date)

insert into MyPeople values(1,1,'Raghu','Dasara','1982-05-07 03:00:00'),

                                           (2,1,'Chaithnay','Dasara','1987-10-07 15:00:00') ,

                                           (3,2,'Pathi','Dasara','1994-10-13 10:00:00'),

                                           (4,2,'Sudhar','Simha','1994-10-13 10:02:00'),

                                           (5,2,'Rahul','Ramghad','1987-05-07 19:00:00')  

SELECT

     [PersonID]

    ,[FamilyID]

    ,[FName]

    ,[LName]

    ,[DOB]

    ,ROW_NUMBER() over (ORDER BY DOB) AS Number

FROM

       MyPeople

Result of the above Query :

        PersonID       FamilyID       FName        LName    DOB        Number

1               1               Raghu      Dasara  1982-05-07  1

5               2               Rahul      Ramghad 1987-05-07  2

2               1               Chaithnay  Dasara  1987-10-07  3

3               2               Pathi      Dasara  1994-10-13  4

4               2               Sudhar     Simha   1994-10-13  5

SELECT

     [PersonID]

    ,[FamilyID]

    ,[FName]

    ,[LName]

    ,[DOB]

    ,ROW_NUMBER() over (ORDER BY DOB) AS Number

FROM

       MyPeople

ORDER BY

       PersonID

Result of the above Query :

PersonID       FamilyID       FName        LName   DOB         Number

1               1               Raghu      Dasara  1982-05-07  1

2               1               Chaithnay  Dasara  1987-10-07  3

3               2               Pathi      Dasara  1994-10-13  4

4               2               Sudhar     Simha   1994-10-13  5

5               2               Rahul      Ramghad 1987-05-07  2

SELECT

      [PersonID]

     ,[FamilyID]

     ,[FName]

     ,[LName]

     ,[DOB],

      ROW_NUMBER() over(PARTITION BY FamilyID,

                         CONVERT(NVARCHAR(25), DOB, 111) -- select SYSDATETIME()--2014-07-10 10:44:46.1160635

                         ORDER BY DOB ASC) TwinCode      --,select CONVERT(varchar(10),SYSDATETIME(),111) --2014/07/10

                                                                                              

  FROM [MyPeople]

       ORDER BY     PersonID

Result of the above Query :

PersonID       FamilyID       FName      LName   DOB         TwinCode

1               1             Raghu      Dasara  1982-05-07  1

2               1             Chaithnay  Dasara  1987-10-07  1

3               2             Pathi      Dasara  1994-10-13  1

4               2             Sudhar     Simha   1994-10-13  2

5               2             Rahul      Ramghad 1987-05-07  1