Showing posts with label mssql. Show all posts
Showing posts with label mssql. Show all posts

How to Create MS SQL Server Database Using Microsoft SQL Server Management Studio and T-SQL?

This article explains How to create a database in Microsoft SQL Server using the MS SQL SERVER Management Studio and T-SQL. This article explains the step by step method to create the database using the Management Studio, beginners in MSSQL can easily create the database using this tutorial.
  1. Open Microsoft SQL Server Management Studio.
  2. Connect to the database engine using database administrator credentials.
  3. Expand the server node.
  4. Right click Databases and select New Database.
  5. Enter a database name
  6. Add / Change Data file path
  7. Add / Change Log file path
  8. Click OK to create the database.






Create database SQL syntax for MS SQL Server is as follows.



SQL for Beginners

Here we would like to share the best 15 Books on SQL for beginners. In amazon below mentioned 15 SQL books got the top reviews and highly recommended for beginners.

Learning SQL: Master SQL Fundamentals
by Alan Beaulieu




















Image source : https://www.amazon.com/
novicetechie.com

 Log Shipping - How you can convert the secondary database into primary
This topic explains the log shipping if the primary database is suspected or corrupted and how you can convert the secondary database into primary database.
To recover a database without restoring (to be done in the master data base)
Execute the RESTORE DATABASE statement, specifying:
  • ·   The name of the database to be recovered
  • ·   The RECOVERY clause


  /* To recover a database without restoring (to be done in the master database)  
   Execute the RESTORE DATABASE statement, specifying:
   The name of the database to be recovered
   The RECOVERY clause

Example
For example, you can recover the database, AdventureWorks2008R2, as in a restore operation without restoring data
*/

-- Restore database using WITH RECOVERY.
RESTORE DATABASE AdventureWorks2008R2
   WITH RECOVERY


Some free database tools are available from IDERA for better and smoother database management and development of SQL SERVER databases. Below mentioned tools are available from IDERA
For monitoring and performance
  • SQL CHECK – Real time performance monitoring for SQL.
  • SQL Fragmentation Analyzer - Detect fragmentation hotspots across SQL Servers.
  • SQL Update Statistics - Update out-of-date SQL Server statistics.
  • SQL Statistics Aggregator - Simplify SQL query tuning.
  • SQL XEvent Profiler - SQL XEvent efficiency with SQL Profiler simplicity.
  • SQL Heat Map - View SQL Server storage utilization.
  • SQL Page Viewer - Easily access SQL Server page data.
  • SQL Query Store Optimizer - Improve SQL Server Query Store performance.
  • MySQL Query Explorer - Monitor and Tune MySQL Queries.
  • SQL Hekaton Memory Check - Monitor SQL Memory-optimized tables.
  • SQL Instance check - Discover SQL Servers and Check for Version Updates.
  • SQL BI Check - Monitor the performance of your SQL BI stack.

For the Backup and Administration
  • SQL Backup Status Reporter - View SQL Server backup history.
  • SQL Integrity Check - Identify SQL Server corruption areas.
  • SQL Job Manager - View and manage SQL Server jobs across multiple servers.
For the Development
  • *NEW* SQL Data Profiler - Analyze data patterns in SQL Server database tables.
  • Rapid Database Extractor - Simplify Oracle and SQL Server database exploration. 


Resource: www.idera.com and newsletter@mssqltips.com

Write T-SQL query with parameters

Example for using parameter in a query

DECLARE @CityParameter  varchar(50)
SET @CityParameter = 'Paris'
SELECT * FROM Person.Address
WHERE City = @CityParameter
testing query output

Shrink database

Shrink Microsoft SQL Server database log file

Consider the following information when you plan to shrink a database:
  • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
  • Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
  • Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
ALTER DATABASE TempDB SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (TempDB_Log, 5)
GO
ALTER DATABASE TempDB SET RECOVERY FULL
GO
© Copyright 2015 Novice Techie. Designed by Bloggertheme9 | Distributed By Gooyaabi Templates. Powered by Blogger.