Upload (Insert) Resume (word document) to SQL Server Table

SQL Server's VARBINARY datatype allows us store BLOB data like Images, word documents etc., Lets see how that works with an example:

1.Create Table:
CREATE TABLE dbo.Consultants
(
ID INT IDENTITY(1,1),
ConsultantName VARCHAR(50),
ResumeDoc VARBINARY(MAX)
)

Once we have the table created, lets insert a resume located in some folder (D:\Steve_Resume.docx) into the table.

2. Query:

INSERT INTO dbo.Consultants (ConsultantName, ResumeDoc)

SELECT 'Steve', * FROM OPENROWSET (BULK N'D:\Steve_Resume.docx', SINGLE_BLOB) Doc

3. Check the Output:
SELECT ID, ConsultantName, ResumeDoc FROM dbo.Consultants

Hope this helps! Happy TSQLing !
Important: I will discuss how to extract the Resume from the SQL Server Table back to a windows folder location in the next post.

Table Size Estimation!!!

Someone says Table is the core object of a database, I would say its the heart of a SQL Server.

Today, I come up with one interesting observation regarding the table size estimation.
The requirement is to design a table to store Customer information for which i have to find the estimated size of the table for 1000000 records.

Table: CustomerInfo

Column Name	Datatype
ID	INT
FIRSTNAME	VARCHAR(30)
LASTNAME	VARCHAR(30)
ADDRESS	VARCHAR(50)
CITY	VARCHAR(15)
STATE	CHAR(2)
ZIP	CHAR(10)

Heres the procedure to estimate the size:

1. Find size of the row :

4+30+30+50+15+2+10 = 141 bytes

2. Divide 8096 by result above:

8096/141 = 57 (# of rows that fit on page)

3. Assuming 1000000 is the record count expected, divide the count with step 2 results:

1000000/57 = 17543.8 ~ 17544 (# of pages)

4. Multiplying # of pages with 8192

17544 * 8192 = 143720448 bytes = 137 MB

And thats how we estimate the size of the table!!!