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

SQL Server Assignment Operator (=)

There is only one assignment operator and that is "=". This is used to assign a value to some expression.

DECLARE @AnExpression INT; SET @AnExpression = 87;

SQL Server Arithmetic Operators (%)

(%) modulo operator is used to get the remainder of one number.

Select 47 / 7 AS IntValue, 47 % 7 AS RemValue;

Result: 6, 5

SQL Server Arithmetic Operators (/)

(/) division operator is used to divide two expressions.

Select id, name, someColumn / 10

SQL Server Arithmetic Operators (*)

(*) multiplication operator is used to multiply two operators.

Select 10 * 7

Result = 70

SQL Server Arithmetic Operators (-)

(-) subtraction operator is used to subtract two numbers, also subtracts a number from date as number of days.

DECLARE @someNumber int; SET @someNumber = 191;
SELECT '200' - @someNumber;

Result = 9 

DECLARE @initialdate datetime, @someNumber int;
SET @initialdate = ''June 26, 1982 07:33 AM';
SET @someNumber = 3;
SELECT @initialdate - @someNumber AS 'Final Date';
Result = 1982-06-23 07:33:00.000

SQL Server Arithmetic Operators (+)

(+) addition arithmetic operator is used to add two numbers, also adds a number either in days or in a date.

DECLARE @someNumber int; SET @someNumber = 191;
SELECT '200' + @someNumber;

Result = 391 

DECLARE @initialdate datetime, @someNumber int;
SET @initialdate = ''June 26, 1982 07:33 AM';
SET @someNumber = 3;
SELECT @initialdate + @someNumber AS 'Final Date';
Result = 1982-06-29 07:33:00.000

Version history in your database using Microsoft Visual Studio 2010

In this blog post you will find a little technique that we use in order to store a version history of our deployments. Please check that this requires a working knowledge of datadude so if you don’t know what the terms SQLCMD variables, Post-Deployment script, msbuild & Continuous Integration means to then this blog post may not be referred to for you :)

First we Need a table

First we need a table for the storage of our version history, in this case lets called it BuildVersion and it definitely looks like the following

CREATE TABLE dbo.[BuildVersion]
(
BuildVersion NVARCHAR(20) NOT NULL,
Deployed DATETIME NOT NULL
)

Second we Need a project variable

Here we have a SQLCMD variable declared as part of our project that is meant to store a version number. By default in datadude SQLCMD variables are declared in a file that is called Database.sqlcmdvars:

we have a variable called ‘BuildVersion’ that are default to the value of “Unknown”

Here we Need to populate the table

We are using the value in our ‘BuildVersion’ variable to fill our BuildVersion table and we do that within a Post-Deployment script here by using the following code sample that you may see what happen next :)

INSERT [dbo].[BuildVersion]([BuildVersion],

[Deployed])
VALUES ('$(BuildVersion)',

GETDATE())

--$(Here BuildVersion is a SQLCMD variable declared within this project)

And here we need to tell our mechanism what the build number really is

First we need to make sure that $(BuildVersion) has a value in it. As we are deploying our database as part of a continuous integration build (leveraging msbuild) then we can pass in a value from our msbuild script. Find the code that we use to do that:

<Exec Command="..\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:&quot;Data Source=$(Server);Integrated Security=True" /DeployToDatabase:+ /ManifestFile:"..\MyDB.deploymanifest"/p:BuildVersion="$(BuildLabel)"" />

The important bit for what we are discussing here is that last part (underlined):

/p:BuildVersion="$(BuildLabel)"

The /p directive is used to populate a named variable (in our case it is BuildVersion) with a value. In our case than value is grabbed from an msbuild property which in this case, is referred to by $(BuildLabel). [Please note that managing msbuild properties is outside the scope of this article.]

Lets Wrap-up

That’s really what you need. If it all remains together correctly then [BuildVersion] will surely contain a nice history of all your deployments

I hope this is useful for you. I guess the same technique will also work in earlier versions of datadude but I don’t have any to hand so can’t about it.

Technology in the line of Extinction

We have heard about the extinction of dinosaurs, mammoths etc. happened billions of years ago but have you ever noticed that you are also a witness of some extinction activities...

Following are few technologies that are on the edge of extinction or some already extinct :)

1. Landline phones

2. Floppy disks

3. Wristwatches

4. VHS tape and VCRs

5. Film cameras

6. Typewriters

7. The Walkman, Discman and MiniDisc player

8. Dial-up Internet access

9. CDs & DVDs

10. Fax Machines

11. Tube TVs & Plasma TVs

12. Beepers

13. Credit Cards

14. E-Book Readers

15. iPods

16. Steam Engines

17. Instant Cameras

18. Computer Monitors

Microsoft SQL Server 2000 Alternative

There is a free version of Microsoft SQL Server 2000 named MSDE (Microsoft SQL Server Desktop Engine) with few limitations

Maximum 2GB database

Slow performance over 8 concurrent connections

Not supported in Windows NT 6.

No GUI management tool

http://en.wikipedia.org/wiki/MSDE

Get application name for current session in SQL server

Get application name for current session in SQL server.

SELECT APP_NAME()

Get current user name in SQL server

Get current user name in SQL server.

SELECT CURRENT_USER

Get current login name in SQL server

Get current login name in SQL server.

SELECT SYSTEM_USER

Get client IP address in SQL server

Get client machine IP address in SQL server.

DECLARE @IP_Address varchar(255)

SELECT @IP_Address = client_net_address FROM sys.dm_exec_connections WHERE Session_id = @@SPID

PRINT @IP_Address