SELECT statement

Day 10

 SELECT statement

 While the SELECT statement offers a plethora of arguments that can make it very complex, in its sim­plest form it consists of two keywords: a list of columns and a table name.

1. Write a SELECT statement.
2. Sort your results.
3. Filter a SELECT statement with the WHERE clause.
4. Use comparison operators.
5. Use the BETWEEN operator.
6. Use the WHERE clause with multiple conditions.
7. Search for a list of values.
8. Use a wildcard search.
9. Create aliases.
10. Use the JOIN operator.
11. Limit the data returned in your result set.
12. Use the UNION keyword to combine result sets.

Write a SELECT statement

1. Open Microsoft SQL Server Management Studio (SSMS) and connect to a server.
2. In Object Explorer, expand the Databases folder.
3. Expand the AdventureWorks2012 database.
4. Expand the Tables folder.
5. Expand the Flowers. Department table.
6. Open the query editor in SSMS.
7. In the query editor, enter and execute the following T-SQL code:

Database
USE Thigalla;
SELECT
 FROM

Click and drag the Columns folder underneath the table in Object Explorer to the right of the keyword SELECT. Ensure that there is a space after the SELECT keyword.
Click and drag the Flowers. Department table from Object Explorer to the right of the keyword FROM. Ensure that there is a space after the FROM keyword.

--Use this query to select data from the Flowers. Department table in the database USE Thigalla; SELECT DepartmentID, Name, GroupName, ModifiedDate FROM [Flowers]. [Department]
8.    Execute the query and review the results.

Filtering data with the WHERE clause
So far, you have simply returned all the rows in a table.In the real world, this is probably not what you will do—most times, and you will need to return only subsets of data.

1. Comparison operators
2. The BETWEEN operator
3. A WHERE clause with multiple conditions
4. A search for a list of values
5. A wildcard search

Creating aliases
You can create aliases, which can be a shorter or more understandable name, for table and column names, making it easier to work with aggregations, expressions, and queries that involve multiple tables.In addition, your database may contain very cryptic column names, and you may want to provide names that are more meaningful to applications and end users.Using aliases allows you to rename or shorten the names of tables and columns.
Alias a table and column

1. Open the query editor in SSMS.
2. In the query editor, enter and execute the following T-SQL code:

--Use this query to alias a table and columnUSE THIGALLA;
SELECT DepartmentID, Name AS DepartmentName, GroupName AS DepartmentGroupNameFROM HumanResources.Department AS d
In the preceding query, the Name column is renamed DepartmentName, the GroupName column is renamed DepartmentGroupName, and the table has been aliased as simply d. Now you can refer­ence the table as d instead of the entire table name throughout the query.The use of table aliases is explained in the next section.

Thanks & Regards

Srikanth Goud- Virtualization Administrator

Enable mixed mode authentication in MS SQL?

Enable mixed mode authentication in MS SQL?

Mixed mode authentication allows you to log into a SQL server using either your Windows VPS username and password or your SQL database username and password. When logged in using your Windows VPS username and password, you have access to all the databases on the server.

           1.       1.Log into your SQL server (Run SSMS)

           2.       Click Start, Programs, Microsoft SQL Server 2012/2008 r2 and select SQL Server  Management Studio Express or SQL Server Management Studio, depending on your version of SQL Server.

         3.      Enter the requested information:

• Server Type: select Database Engine
• Server Name: this field should be populated by default
• Authentication: select Windows Authentication

          4.       Right click the server name and select Properties.

           5.       Click Security,

         6.       Under Server authentication select SQL Server and Windows Authentication Mode.

         7.       Click OK.

         8.      Right click the server name and select Restart. Wait a few moments for the service to restart before proceeding.

Thanks & Regards

Srikanth Goud- Virtualization Administrator

Database snapshots

Day -9

Database snapshots

A database snapshot is a static, read-only copy of an existing Microsoft SQL Server database.The existing database is referred to as the source database when discussing database snapshots.When a snapshot is created, it is an exact read-only replica of the source database at that point in time. As the source database changes, the snapshot will be updated to ensure that it is synchronized. When creating a database snapshot, you must ensure that your source database constantly and con­sistently remains available.

1.      Database snapshots are supported only in the Enterprise version of SQL Server 2012.

2.      The source and the snapshot database must reside on the same SQL Server instance.

3.      The source database cannot be dropped, detached, or restored.

4.      Source database files cannot be dropped.

5.      Performance could be negatively affected due to increased I/O on the source.

List of the common snapshot limitations

1.      The snapshots must reside on the same server as the source database.

2.      Snapshots cannot be backed up, restored, or detached.

3.      Changes in the source database will cause the snapshot database to grow. Therefore, you should ensure that you have disk space available equal to the size of your source database.

4.      If a snapshot runs out of space, it must be deleted and re-created.

View a database snapshot with SSMS

1.     Open SSMS and connect to a server.

2.     In Object Explorer, expand the Databases folder.

3.     Expand the Database Snapshots folder.

Dropping database snapshots

At some point, you may decide that a database snapshot is no longer useful, or you may decide that it is consuming too much space and you would like to start with a fresh snapshot.

Drop a database snapshot using SSMS

1.     Open SSMS and connect to a server.

2.     In Object Explorer, expand the Databases folder.

3.     Expand the Database Snapshots folder.

4.     Right-click the database snapshot that you want to drop, and select Delete from the context menu.

5.     Check the box labeled Close Existing Connections

Reverting to a database snapshot

One of the biggest advantages you can leverage by creating database snapshots is that you may be able to use them as a backup to a database backup. Restoring from a database snapshot is a viable option in some cases, but it is not a complete copy of the database. Therefore, if the source database becomes corrupt, you cannot revert to the snap­shot.

1.     The source database must have only one snapshot.

2.     If any of the files are read-only or offline, you cannot revert to a snapshot.

3.     Any changes that occurred after the reverted snapshot was taken will be lost.

Thanks & Regards

Srikanth Goud- Virtualization Administrator