Assignment # 01
By
AQIB RAZA
CIIT/FA19-BCS-126/ATK
Teacher: Mr. WASEEM KHAN
Submission Date: Monday, March 1, 2021
COMSATS University Islamabad
Attock Campus
Spring, 2021
DIFFERENCE BETWEEN MDF & LDF
1.MDF is the primary data file for MSSQL. The LDF, on the other hand, is a supporting file and is
characterized as a server transaction log file.
2.MDF contains all the vital and necessary information in databases while the LDF contains all
the actions which include transactions and changes made in the MDF file.
3.LDF is concerned with three operations while MDF is not.
4.The LDF file size can take up a lot of space due to the numerous changes and the information
details ascribed to the changes while the MDF file can maintain or a change in file size
depending on the actual changes made to the file itself.
SQL SERVER DATA TYPES
We discussed only important data types of SQL server.
Exact numeric SQL Server data type
We use exact numeric data types for integer, decimal, and money. Each data type has its own
lower, upper limit and memory requirements. We should use the smallest data type to save
memory requirements as well. For example, we can use the bit data type for storing true (1) or
false (0) values.
Data Storag
Type Lower Range Upper Range e Remarks
We can also store NULL values in
Bit 0 1 1 byte this.
We can store whole numbers up to
tinyint 0 255 1 byte 255 in this data type.
We can store whole numbers
Smallint -2^15 (-32,768) 2^15-1 (32,767) 2 bytes between a lower and higher range.
It also stores the whole number
−2^31 (−2,147, 2^31−1 (−2,147, similar to a smallint but its lower
Int 483,648) 483,647 4 bytes and upper limits changes as defined.
We should use bigint data type if we
−2^63 (−9,223,372, 2^63−1 (−9,223,372, cannot accommodate data in the
Bigint 036,854,775,808) 036,854,775,807) 8 bytes integer data type.
Approximate numeric SQL Server data type
Data Lower Upper
Type Range Range Storage Remarks
We can use float924) as ISO
Real −3.40E+38 3.40E+38 4 bytes synonym for real.
Its storage depends upon
value (n)
It is an Approximate-number data
N(1-24) ->4 bytes types.
Float(n) −1.79E+308 1.79E+308 N(25-53)->8 bytes The default value of N is 53.
Date and Time SQL Server Data types
We stored date and time data along with time offset using these data types.
Lower
Data Type Range Upper Range Storage Remarks
1. It stores only dates in SQL Server.
2. Its default value is 1900-01-01.
3. It provides default format
Date 0001-01-01 9999-12-31 3 bytes YYYY-MM-DD.
Datetime2 0001-01-01 9999-12-31 6-8 bytes 1. the default format for this is
00:00:00 23:59:59.9999999 YYYY-MM-DD hh:mm: ss[.fractional
1. Precision<3 ->
seconds].
2. It provides precision from 0 to 7
6 bytes digits, with an accuracy of 100ns.
2.Precision 3 or 2. The default precision for
4-> 7 bytes datetime2 is 7 digits.
Character Strings SQL Server Data types
Lower
Data Type Range Upper Range Storage Remarks
0 1. It provides a fixed-width character data
Char(n) characters 8000 characters N bytes type.
1.It is a variable length character data type.
0 n bytes + 2
Varchar(n) characters 8000 characters bytes 2.N defines the string size.
We should avoid using this data type unless
Varchar 0 n bytes + 2 required due to its huge storage
(max) characters 2^31 chars bytes ~ 2 GB requirement.
1. It is a variable-length character data type.
2. We should avoid using this data type as it
2,147,483,647 n bytes + 4 might get deprecated in future versions of
Text 0 chars chars bytes SQL Server.
