how does sql server work.

*this is a study guide that was created from lecture videos and is used to help you gain an understanding of how does sql server work.

Microsoft SQL Server Foundations

What is SQL Server?

Relational database management system, stores and retrieves data in databases, creates and administers database components, controls access by users with varying permissions

System administrator- configures the server environment, maintains physical hardware, manages top-level user accounts

Database administrator- performs database backups, manages database-level user accounts

Microsoft SQL Server 2016 uses the Transact-SQL version of the language, which is used to filter, sort, combine, add, update, and retrieve records from the database.

SQL Server editions- enterprise, standard, express and developer

Configuration manager is used to stop and start the server,

Log in to the server- MSMS, Microsoft Management Studio -> database engine -> server instance -> windows authentication

Sa is system administrator login account -> right click -> properties -> status -> enable login -> right click in logins -> refresh

Enable sql server authentication mode right click on instance -> properties -> securities -> sql server authentication mode -> restart server

Restart server- start menu -> sql server configuration manager -> sql server services -> server instance -> right click for properties -> restart

Transact-SQL REference from technet.microsoft.com – Login -> help -> add and remove content -> add documentations that you want to download packages

SSMS Interface

New query, drop down list for the database to query,

You can add toolbars under view tab, this is crucial to learn how does sql server work.

Left side is object explorer windows- gives easy access into instance

Create a new database- right click on database in object explorer window -> new database -> general tab -> database name -> (click on “…” to change settings such as to chnge the file growth or maximum file size)

SQL Data Types

Horizontal rows- store information about a single item, typically called records

Vertical columns- attribute fields, descriptive component of each record

Text data types- char(10) is text data of 10 characters, nchar(10) is unicode text with 10 characters, varchar(50) is variable length text data up to 50 characters and nvarchar(50) is variable length unicode text data up to 50 characters

Numerical data types- tinyint- whole numbers only between 0 and 255, int is whole numbers only between -2 billion and +2 billion, decimal or float is fractional numeric with varying degrees of position

Specialty data types- date, currency, geographic coordinates, etc.

Create a data table- find database in object explorer -> find tables tab -> right click and choose “table” -> create column names and data types (set primary key with button in top right) -> use Camel casing (FirstName is example of how to name a column)

Assign numbers automatically- when creating table -> column properties -> identity specification -> (is identity) -> set to yes -> identity seed sets the automatic first number assigned

Access and edit table info- Right click on Table -> click design

Join tables together with keys

Create table and use same column attributes for two tables such as CustomerID, int. -> top right, “relationships” button -> add new relationship -> click tables and columns specific “…” button

To get a better understanding of SQL Server before learning commands visit techtarget.

Transact-SQL Commands

Select statement- go into tables -> right click -> select top 1000 rows -> new query button and create query ->

 

SELECT OrderID, CustomerID, OrderDate

FROM Sales.Orders

WHERE CustomerID = 578

ORDER BY OrderDate DESC

 

->

Choose Database from dropdown -> click execute

Use functions to calculate views

Choose table -> right click and select top 1000 rows -> new query …

 

SELECT OrderLineID,

  Quantity,

  UnitPrice,

  TaxRate,

  Quantity*UnitPrice AS ExtendedPrice,

  Quantity*UnitPrice*(TaxRate/100) AS TaxDue,

  Format( (Quantity*UnitPrice) + (Quantity*UnitPrice * (TaxRate/100)), ‘C’) AS TotalPrice

FROM Sales.OrderLines

 

“Quantity*UnitPrice AS ExtendedPrice” will multiply the quantity column and the unit price and will be named extended price.

Format function shown in pink will change from standard number to currency value ( ‘C’)

To see more functions, visit the documentation “Built-in Functions (Transact-SQL)” at technet.microsoft.com

Write an update statement

Choose table Quantity*UnitPrice select top 1000 rows -> new query..

 

UPDATE Sales.SpecialDeals

SET EndDate = ‘12/31/2016’, DealDescription = 10% 2016 USD Wingtip’

WHERE SpecialDealID = 1

 

SELECT *

FROM

Sales.SpecialDeals

 

Add Data with an INSERT statement

Choose table -> select top 1000 rows -> new query..

INSERT INTO Sales.SpecialDeals (SpecialDealID,

         DealDescription,

         StartDate,

         EndDate,

         DiscountPercentage,

         LastEditedBy,

         LastEditedWhen)

VALUES (‘3’, ‘25% off in October’, ‘10/1/2016’, ‘10/31/2016’, ‘25’, ‘1’, ‘7/20/2016)

SELECT * FROM Sales.SpecialDeals

 

Create a view of the data

Choose Views from object explorer  -> click add for every table you want to -> (adjust the panes in the top right) -> remove relationships that you don’t want by right clicking and choosing remove -> checkmark the columns to add them to the criteria pane -> click execute SQL button in top right -> Save view -> refresh view in object explorer

Learning Transact-SQL

Using SQL Server Management Studio

Metadata- data about data

Windows Authentication- provides authentication through the account that your workstation is under

SQL Server Authentication- credentials inside of SQL Server

Object Explorer- window for browsing and managing objects, organizes database objects into folders, buttons for connecting and disconnecting

Database- collection of data, data must be organized, data must be related, model of an aspect of reality, tool to transform data to information

Tables- units of storage

Views- defined virtual tables

Programmability- embedded procedural programs

Database Security

Schemas- container of database objects, provides security context, always reference the object name and the schema name

Object Naming Convention- Server.Database.Schema.Object

SELECT * FROM Server.Database.Schema.Object

Columns in a table- primary properties are column name, data type and null allowed; secondary properties are key(s) and computed

Defining data with data types

Characters- (A-Z, 0-9, all special characters)

Char, varchar, varchar(max)

Integers- (non decimal values) used for whole numbers

Tinyint, smallint, int, bigint

Exact numeric- (decimal values) used for decimals

Decimal, numeric, money, smallmoney

Data and time- used for date and times

Datetime, smalldatetime, datetime2, date, time, datetimeoffset

Approximate numeric- store real big or real small numbers that are approximate

Float, real

Binary- either on or off such as sounds or video data

Bit, binary, varbinary, varbinary(max)

NULL Property

Special Marker, Data value does not exist, set and tested with NULL keyword, three-valued logic: true, false, unknown

Use Select Top 1000 or edit top 200 to easily display table data

Tools -> options -> sql server object explorer -> commands, used to change select top 1000 or edit top 200

Identifying Table Relationships

Primary Key- uniquely identifies a row, one per table, can consist of one or more columns, cannot be null

Foreign Key- points to a primary key column, provide consistency between tables, enforced by SQL Server, Predefined Path through the database

Relationship Types

One to one/1:1- 2 tables for every row there can only be one matching row

One to many- for every row in one table you can have 0,1 or many rows

Many to many- many rows in one table and many rows in the other table, requires a third table or a join table

You can also have self-referencing relationships, which is used with one table that has both a primary and foreign key of itself.

SSMS Diagram Support

Diagrams create a visual representation of the data.

In object explorer under the specific database, right click on database diagram and select install diagram support. You need to be a system administrator. After create new database diagram -> highlight tables you want on the add table window -> save the diagram with save icon on toolbar.

Modify the diagram by right clicking and select modify. View Page Breaks button on toolbar to prepare diagram for printing. You can also adjust the zoom for the diagram.

Preparing to Write SQL

Line Numbers for Queries- Tools -> Options -> Text Editor -> Transact-SQL  ->Display, single click URL navigation

Highlight the query to specify on that script. Type in the table first, the columns will then use Intellisense.

For more details on Transact-SQL, you can visit Microsoft to complete learning of how does sql server work.