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.
Your Mind Moves the Machine.