What is table and what are the items to consider while creating table in SQL Server?
Tables are basic building blocks of Database. Tables will be used to store data in to database.
Points to remember while creating table in SQL Server
• You should be able to separate data in to different columns in table and assign corresponding data types to those columns.
• The amount of space consumed on disk
• The amount of memory consumed when processing data
• The queries required to manipulate the data for application use
What are the different data types available in SQL Server?
Numeric: Stores numeric values.
Monetary: It stores numeric values with decimal places. It is used specially for currency values.
Data and Time: It stores date and time information.
Character: It supports character based values of varying lengths.
Binary: It stores data in strict binary (0 or 1) Representation.
Special purpose: SQL Server contains Complex data types to handle the XML Documents, Globally unique identifiers etc.
What are the specialized data types in SQL Server?
bit :Stores a 0, 1, or null. Used for basic “flag” values.
TRUE is converted to 1, and FALSE is converted to 0.
Timestamp: An automatically generated value. Each database contains an internal counter that designates a relative time counter not associated with an actual clock. A table can have only one time stamp column, which is set to the database timestamp when the row is inserted or modified.
Uniqueidentifier: A 16-bit GUID used to globally identify a row across databases, instances, and servers.
sql_variant: It can change the data type based on the data that is stored within it.
Cursor: Cursor is used by applications that declare cursors. It contains a reference to the cursor that can be used for operations. This data type cannot be used in a table.
Table: It is used to hold a result set for subsequent processing. This data type cannot be used for a column. The only time you use this data type is when declaring table variables in triggers, stored procedures, and functions.
Xml: It stores an XML document of up to 2 GB in size. You can specify options to force only well-formed documents to be stored in the column.
Very useful