SQL Server is one of the RDBMS products in the market. Every application requires one database server like Oracle, SQL Server, MySQL, HANA, DB2 etc. SQL Server is second most used database server in the world because of its features and functionalities. It is easy to navigate graphical user interface application, even layman can learn it easily.
Do you want to learn SQL Server?
Many topmost banks, ecommerce websites, corporate websites and many more websites are using the SQL Server as their database server because of license cost, security, backup and restore and high availability features.
If you learn SQL Server, there are plenty of opportunities in the market as a database developer and database administrator.
Every RDBMS has two parts.
Need of SQL Server:
If we want to perform day to day transactions, we need database server. Day to day transactions means insert, update, delete, select and these are called CRUD operations
Operating System is a software; it manages the hardware.
DBMS software manage database
Using DBMS software users can create and maintain databases.
Development of SQL Server consists of two parts.
Inside the Server we have to install software like SQL Server, in this we have to create objects like databases, tables, stored procedures, functions, triggers and cursors etc.
SQL Server follows the client-server architecture.
SQL languages used to interact with database servers.
- DDL (Data Definition Language): Create, Alter, Drop, truncate
If we want to create tables, alter tables, alter tables, drop tables and other objects we use DDL commands
2. DML (Data manipulation Language): Insert, Update, Delete
By using this we can insert, update, and delete actual data.
- 3. DRL (Data Retrieval Language): Select
We fetch the data by using select.
- 4. DCL (Data Control Language): Grant, Revoke, Deny
We can give access to the database objects.
- 5. TCL (Transaction Control Language): Commit, Rollback, Savepoint.
Data types in SQL Server:
The type of the data that we store in memory is called data type.
- Integer: tiny int, small int, int, big int.
- Decimal: Allows numbers contain decimal
- Currency: small money, money.
- Data and time: small date time, date time, date, time.
- String: char, varchar, nchar, nvarchar.
- Binary: allows pictures up to 800 bytes.
Timestamp: A column declared with time stamp data type changes automatically whenever record is updated.
Unique identifier: Globally uniquely identify rows.
XML: used to store data in XML format.
Purpose of integrity constraints prevents users from entering invalid data.
Constraint means rule and integrity means data correctness. Rules to maintain data correctness or data integrity.
According to RDBMS integrity constraints are 3 types
- Entity integrity (Unique, primary)
- Domain integrity (Not Null, check, default)
- Referential integrity (Foreign key)
According to SQL Server integrity constraints are
- Not null
- Primary key
- Foreign key
Built in functions in SQL Server:
A function is a block of code that accepts some input, perform some task and return a value.
SQL Server functions are categorized into 2 types
- Single Row functions (scalar functions): These functions process single row and return one value per row.
- Multi Row functions (Group functions)
Joins in SQL Server:
Join is a data retrieval operation. If we want to retrieve data from more than one table, then we use joins. In OLTP database tables are normalized, related data spread across into multiple tables, together data table need to be joined.
- Inner join
- Outer join
- Self join
- Non-equi join
- Cross join
Sub Queries in SQL Server:
A query embedded in another query is called sub query or nested query.
One query is called outer/parent/main query.
Another query is called inner/child/sub query.
First Inner query is executed then result of inner query is input for outer query.
Outer query can be insert, update, delete, select.
Inner query must be always select.
Types of Sub Queries:
- Single row sub query
- Multi row sub query
- Co-related sub query
Derived Tables: Sub queries in “From” clause are called derived tables.
Use Derived tables:
- To access column aliases in where clause.
- To use the result of one process in another process.
Views in SQL Server:
A view is a subset of a table, it is like virtual table, stored query.
A view includes specific rows and specific columns from table.
Views are created for security, Reduce complexity.
Views are 2 types
- Simple view
- Complex view
If view based on single table, then it is called simple view
Purpose of creating view is we want only specific data for the user not total data.
If we based on multiple tables or query contains group by clause, having clause, distinct, aggregate function then view is said to be complex view.
What is the difference between simple view and complex view?
Simple views are updatable (Allow DML operations), complex views are not updatable
Indexes are created to improve the performance of data access or data retrieval. Index improves performance of “SELECT” operation.
Parsing means checking syntax and semantic errors. Optimization means prepare different plans to execute query and select the best plan.
A synonym is another name or alternate name for a table.
Synonyms are created if table name is lengthy.
Difference between table alias and synonym?
The scope of the alias is up to the query only can not be accessed outside the query.
Synonyms can be accessed in any query.
Alias is not stored in database but synonym I stored in database.
Cursors in SQL Server:
Cursors are used to process multiple records, use cursors to do row-by-row processing.
Cursor is a name given to the context area. When user submits select statement to SQL Server, Server executes the select statement and returns data, data returned select statement is loaded into temporary memory are called context are.
Using cursor, we can give name to the context area. Using cursor, we can fetch one by one record from context area and process the record.
Stored Procedures in SQL Server:
A stored procedure is a named TSQL block, that accepts some input, performs some tasks and may or may not returns a value.
Stored procedures are created to perform one or more DML operations over Database.
Any real-life transaction implemented by using stored procedure. One stored procedure for one transaction.
Functions in SQL Server:
A function is a named TSQL block that accepts some input, performs some task and must return a value.
Functions are created to get values from database and to do calculations.
Triggers in SQL Server:
A trigger is a named TSQL block executed implicitly. Triggers are created to implement complex business rules and validations, to provide security and to do auditing.
SQL Server Administration:
Database administrator is a person responsible for the database design, implementation, maintenance, and repair of the database.
The main goal of DBA is to keep the database server always up and make it available to users. In case of any failures DBA should minimize data loss by implementing powerful backup and restoring techniques.
As a DBA we must perform these
- Maintaining the availability of databases by minimizing the down time.
- Data recovery, we must minimize the data loss in case of failures by implementing high availabilities.
- Provide high security in accessing the databases externally.
- Need to monitor the performance of Server, implement various techniques to increase the performance.
- Regularly Monitor database growth, disk space, SQL Server logs, Event viewer logs to avoid issues and to identify any bottlenecks.
The primary responsibility of DBA is installation of software, depending on the requirement we have install the SQL Server instance in virtual machine or bare machine. There are two licensing models in SQL Server
- Core based licensing
- CAL license (Total Server license + each client license)
Backups in SQL Server:
Backup means exact copy of database.
Situations where backup can use
- Hardware or software failure
- Natural disaster
- Internal misuse
Hardware failure includes operating system, CPU, Memory, network card failures
Software failure includes operating system failures, database server failures. We have one more failure called storage failure.
Natural disaster includes natural calamities like cyclones, fire attacks.
Internal misuse means some one in the organization intentionally or by mistake delete some data.
- To recover lost data.
- To minimize the data loss.
- To minimize the down time.
- Full backup
- Differential backup
- Transactional backup (T log)
- Tail log backup
- Mirrored backup
- Stripped backup
- File/file group backup
- Copy only backup
Restoration is a process of using backups to recover the exist database or to create a new database in another server.
Situations for restoration:
- Planned restoration
- Unplanned or accidental restoration.
Security in SQL Server:
Security is the most important tasks for a DBA. We need control the access to SQL Server as well as provide security to the data.
There are mainly 3 levels of security.
- Server level
- Database level
- Object level
For providing security we have 3 processes:
- Authentication: Validating credentials on server.
- Authorization: Permissions to perform their operations.
Automation in SQL Server:
Automation refers to scheduling the regular maintenance tasks. Whenever schedule time comes automatically that task will be executed. DBA regular activities can be automated. We can get the status of the task in the form of alert and helpful while generating reports.
Automation can be done 2 ways:
- Maintenance plans
Migration in SQL Server:
Migration means moving one or set of databases from one server to another server.
Scenarios for migration:
- As a part of Launch, deployment, go live
Migrating databases from development to test and test to production, this is called as launch.
Whenever new builds released to production as a part of enhancement we call as deployment.
Whenever user can access the application online, we call as go-live.
Upgrading: Means Migrating SQL Server old version to new version.
Log Shipping in SQL Server:
Shipping transaction log files from primary server database to secondary server database to get both databases in continuous synchronization. It is a continuous process in the form of batches.
Database Mirroring in SQL Server:
Maintaining two synchronized copies of database is called mirroring. Secondary database will be in no recovery always. Only one copy of database is available to users at any time.
Replication in SQL Server:
Replication mainly used as a data migration technology although it is one of high availability option. The concept of replication is to migrate data an database objects from one server to another server.
Clustering in SQL Server:
Clustering is a windows level high availability option to increase the availability of physical server hardware, operating system, and SQL Server instances. If any of these 3 fails on one server, the other server in cluster automatically takes tasks of failed SQL Server instance
A windows failover cluster can support up to 16 nodes in SQL Server 2012. Cluster reduce down-time to minimum. Most clustering deployment is between 2 servers only. The clustered servers called as nodes.
Always ON in SQL Server:
Always ON is new high availability and disaster recovery feature in SQL Server 2012. Initially it named as HADRON (High Availability Disaster Recovery ON). It uses clustering technology for failover and mirroring in keeping multiple mirrored copies of the database. In Always ON we can failover multiple databased at the same time using Availability groups.