How To Get/Retrieve Size Of Tables In SQL Server Databases

How To Get/Retrieve Size Of Tables In SQL Server Databases

Sometime we wonder why an application database grew so heavy during the short period of development and testing process. Here most of us wanted to know what are the actual stats of used space by each table and if we need this information without the use of Management Studio's table properties dialog, then follow this...

In fact SQL Server gives us everything we need with a Stored Procedure that is called sp_spaceused. But this SP does not support iterating over the tables in some database, so we needed to leverage another Stored Procedure sp_msForEachTable, but this is undocumented.

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- Get Total DB size.

EXEC sp_spaceused

-- Table row counts and their sizes.

CREATE TABLE #tempTBL

(

[nameOfTable] NVARCHAR(128),

[rowsInTable] CHAR(11),

reserved_size VARCHAR(18),

data_size VARCHAR(18),

index_size VARCHAR(18),

unused_size VARCHAR(18)

)

-- Insert into temp table

INSERT #tempTBL EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

-- Show it to user

SELECT * FROM #tempTBL

-- Number of rows

SELECT SUM(CAST([rowsInTable] AS int)) AS [rowsInTable] FROM #tempTBL

-- Drop the temp table

DROP TABLE #tempTBL

.

Update Statement in SQL

This is used when you want to update records in database table.

This will update column1 and column2 in a table for all rows/records.

Update Table_Name set column1 = value1, column2 = value2


This will update column1 and column2 in a table for the records that are filtered by using the criteria in WHERE clause.


Update Table_Name set column1 = value1, column2 = value2 Where columnX = valueX

Insert Into statement in SQL

This is used when you want to insert new record into database table.

You may write this statement in two different styles.

INSERT INTO my_table VALUES (value1, value2,...)
This is used when you don't want to specify column names during insert operation. so you have to specify the values for columns in same sequence as they appear in database table.


INSERT INTO my_table(column1,column4) VALUES (value1, value4)
This is used when you exactly want to specify column names for which values will be inserted during insert operation.

Order By Keywork in SQL

The keyword "order by" is used to sort the result set.

This sorts a specified column and by default it sorts in ascending order but you may specify the word "Desc" to get result set in descending order.

For ascending sort:

Select column(s) From Table_Name order by column1

Select column(s) From Table_Name order by column1 asc

For descending sort:

Select column(s) From Table_Name order by column1 desc

AND & OR Operators in SQL

In where clause you may filter records but if you want to filter on the basis of more than one condition then you may use "AND" or "OR" operators.

The AND & OR Operators

The AND operator displays a record only when the first condition and the second condition both are true
The OR operator displays a record only if the first condition or the second condition is true

Select column(s) From Table_Name where column1 = value1 AND column2 = value2

Select column(s) From Table_Name where column1 = value1 OR column2 = value2

You may also use the combination of both operators.

Select column(s) From Table_Name where (column1 = value1) AND (column2 = value2 OR column3 = value3)

Where clause in SQL

This "where" clause is used to filter results.

When you want to select, update or delete records, you may use where clause to filter records on which operation will be performed. It is to filter result set on the basis of some criteria, or you may say that it will fetch only those records that fulfill criteria.

Select column(s) From Table_Name 

     Where column = value

"=" can be replaced with any of the following operators.

=	Equal
<> or !=	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Select statement in SQL

Select statement is used in SQL to select data from database objects. You will get a result table on the execution of select statement, which is called result-set.

Select column(s) From Table_Name

or 

Select * From Table_Name

First will return only specified columns and second will return all columns in a table. 

select and SELECT are same as SQL is not case sensitive.

Select Distinct: 

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

Some columns may have duplicate data, select statement returns all occurrences of data whereas select distinct is used when you want to avoid duplicate records/data in result set.

This is applied on column for which you want to filter duplicate records.

Select Distinct column(s) From Table_Name

Syntax of SQL

Syntax of SQL statement is as follows...

SELECT * FROM TABLE_NAME

where select is representing the action and * is the required data for which action will be performed.

TABLE_NAME is the object on which action will be performed. It can be a table name, view, stored procedure etc.

SQL statement is not case sensitive.

Some databases has restriction to apply semicolon at the end of each statement, but microsoft access and SQL server do not apply this restriction.

SQL statements can be divided into two parts.

Data Manipulation Language (DML)

- Select

- Update

- Delete

- Insert Into

Data Definition Language (DDL)

- Create Database

- Alter Database

- Create Table

- Alter Table

- Drop Table

- Create Index

- Drop Index

Meanings of SQL

What is SQL ?

- SQL is the abbreviation of structured query language

- SQL is used to access and control/manage databases

- SQL is a standard called ANSI (American National Standards Institute)

What actions SQL may perform in a database?

- SQL may create new tables

- SQL may create stored procedures

- SQL may create views

- SQL may execute query

- SQL may fetch data

- SQL may insert records

- SQL may update records

- SQL may delete records

- SQL may create new databases

- SQL may set permissions on tables, procedures, and views

The base of SQL is RDBMS, which is the abbreviation of "Relational Database Management System" and this is also a base for all modern database systems like MS SQL, MySQL, IBM DB2, Oracle and Microsoft Access.

In RDBMS the storage of data is in database objects that are called tables.

A table in this case is a collections that has related data entries and its structure is made of rows and columns.

Location of Data Files in SQL Server

You may get the physical location of MDF and LDF files by using following query.

select name,physical_name from sys.master_files

You may see other properties of databases i.e. size, maximum size, growth etc by using "select *" on sys.master_files.

RFID Working Frequencies

RFID works under following frequency standards. Each has a different reading range and other characteristics, and used for different kind of indoor and outdoor applications.

1. LF (Low Frequency)

------------------------------
Radio wave having a long wavelength

30 KHz to 300 KHz

LF readers mostly use 125-134 KHz

Reading range is up to 1.5 ft

This is used for applications such as product authorization, asset management, animal tracking and access control

This may work fine around water or metal objects, but is has short reading range and slower

2. HF(High Frequency)

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

Range of radio frequencies between 3 and 30 megahertz

3000 KHz to 30MHz

HF readers mostly use 13.56 MHz

Reading range is up to 3 ft

This is used for applications such as library books, clothing management and smart cards

This may work fine around water and cost of tag is low, but is has reading issues near metal objects

3. UHF (Ultrahigh Frequency)

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

Range of radio frequencies between 300 and 3000 megahertz

300 MHz to 3000 MHz

UHF readers mostly use 860 MHz to 930 MHz

Reading range is between 10 to 20 ft

This is used for applications such as animal tracking, asset tracking, carton tracking and electronic toll collection

EPC standard based on this frequency with long reading range but have reading issues near water and metal object

4. SHF (Superhigh Frequency)

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

Range of radio frequencies between 3 and 300 gegahertz

UHF readers mostly use 2.4 GHz

Reading range is up to 30 ft

This is used for applications such as Fleet vehicle management, electronic toll collection and airline baggage management

This has long reading range with great data transfer rate and fastest reading capabilities but have reading issues near water and metal object

Mifare Standards

Mifare is a term for NXP semiconductors that are used under the ISO standard 14443 for Type-A 13.56 MHz contactless smart cards or proximity cards.

ISO 14443 also has 4 sub parts that differentiated as per aspect of the some interface

ISO 14443-1:2000 Physical characteristics

ISO 14443-2:2001 Radio frequency power and signal interface

ISO 14443-3:2001 Initialization and anti-collision

ISO 14443-4:2001 Transmission protocol

Following is the list overview of mifare smartcard ICs standards.

MIFARE Ultralight

This can be easily used as disposable public transport tickets. This only has a unique 7 byte serial number with no memory for user data storage.

MIFARE Ultralight C

This is same as mifare ultralight but with the option of open cryptography and anti-cloning functionality.

MIFARE Classic 1K

This is mostly used for access management applications. This has a unique serial number either 4 Byte or 7 Byte along with 1 kbyte EEPROM out of which 768 Bytes are available.

MIFARE Classic 4K

Another version of mifare classic with 4 kbyte EEPROM out of which 3480 Bytes are available.

MIFARE Plus

Another version of mifare classic that comes with more security. This has 2 or 4-KBEEPROM with 2 AES keys (2 x 128 bit sector)

MIFARE DESFire EV1

This is fully compient with 14443 A 1-4. This has fast programming feature with 2 Kbytes, 4 Kbytes and 8 Kbytes EEPROM, and crypto algorithms i.e. DES, 3DES, 3KDES and AES.

Mifare Standards History

Mifare is a standard of NXP semiconductors that are used under the ISO standard 14443 for Type-A 13.56 MHz contactless smart cards or proximity cards.

Following is the history of mifare advancements...

1994 > MIFARE Classic 1k

1996 > MIFARE Classic 1k (transport scheme)

1997 > MIFARE PRO (Triple DES)

1999 > MIFARE PROX (PKI coprocessor)

2001 > MIFARE UltraLight

2002 > MIFARE DESFire

2004 > MIFARE DESFire SAM

2006 > MIFARE DESFire EV1

2008 > MIFARE Plus

2008 > MIFARE Ultralight C

2010 > MIFARE SAM AV2