SQL SERVER

SQL SERVER

1. Relational Databases

   • Used primary keys to be unique, there can not be duplicate rows

2. Constraints

   • Primary Key: Its value cannot be repeated in any row Nullable: If it will accept null as a value

3. Data Types

   • Text:  char(n) fixed length non-unicodevarchar(n) variable length non-unicodenchar(n) fixed length unicodenvarchar(n) variable length unicode, , Date
   • Integer: tinyint: 0 to 255  (1 byte) smallint: -2^15 to 2^15-1 (2 bytes) int: -2^31 to 2^31-1 (4 bytes) bigint: -2^63to 2^63-1 (8 bytes)
   • Decimal: numeric or decimal [(p,s)]: precision is the total number of digits that will be stored ignoring the decimal point, while scale is the number of digits after the decimal point. They are both optional, default p is 18 and scale is 0, this is pretty much an integer since it has no decimals. from 5 bytes to 17 bytes. money: 4 decimal places(8 bytes) from -922 trillion to 922 trillion aprox. smallmoney: 4 decimals(4 bytes) from -214 thousand to 214 thousand
   • Date and Times: Date: from 0001-01-01 to 9999-12-31 (3 bytes) Time[(n)]: n = 0 to 7 (5 bytes) Datetime: from Jan 1, 1753 to Dec 1, 9999 (8 bytes) smalldatetime: from Jan 1, 1900 to Jun 6, 2079 (4 bytes) datetime2(n): its like a combination of date and time types. 6 to 8 bytes datetimeoffset: like datetime2 pluss timezone. 10 bytes

4. Entities

   • Each row of a table represents an entity or set and every colummn represents an attribute.

5. Scalar values

   • Tables stores scalar values (whole numbers or decimals), if you wanted to save a pair of coordinates x,y in one single colummn as a string, that wouldnt be a good practice, instead you should be saving x in one column and y in another.
   • Since the tables always sort elements out if you put a non scalar which is an element that cannot be sorted out you are not doing good practices.

6. RDBMS

   • Relational Database Management System is the intermediary between the clients asking for the data and the actual data. The RDBMS is responsible for protecting and serving the data

7. DML

   • Data Manipulation Language: Includes SELECT, INSERT, UPDATE, DELETE
   • SELECT attribute FROM table or other query WHERE certain conditions of the atributes are met. Can be used to join tables and to create new tables 
   • INSERT INTO table VALUES (list of atributes from the entity) you can specify some attribute to be null if its missing or a default that has been previously determined.
   • UPDATE Table SET [Attribute] = 'New Value' WHERE [otheratt] = 'value' and ..., can be combined with 
   • DELETE table WHERE att = 'value' Delete only deletes the entities of a table, not the table itself  
   • JOIN is a feature from the SELECT command

8. Normalization

   • Is the process of organizing a database to reduce redundancy and improve data integrity

9. DDL

   • Also know as Data Definition Language, its a language used to describe the data stored in our database and to manage our database. Includes CREATE, ALTER and DROP
   • CREATE TABLE: Define the columns and its types and if any of them have a default, primary key or accept or not null
   • ALTER TABLE: We use it to add or remove columns, this is the reason why its not recommended to use * on select commands.
   • DROP TABLE: Destroys a table and all its data