In this article, I am going to discuss about the basics of SQL server which is helpful for newbies.
SQL–Structured Query Language (pronounced as SEQUEL) is a Database, computer language designed for managing data in relational database management system (RDBMS).
Most of the people have to know SQL server query has designed for set based (declarative) .
The SQL commands are
DML (Insert, Update, Delete)
DDL (Create, Alter, Drop, Truncate)
DCL (Grant, Revoke, Deny)
TCL (Commit, Rollback)
A Database stores all the data information (like objects) and has the default data file and log file. The data file is called mdf, ndf…. (One-mdf,more than one ndf). The log file is called ldf (More than one ldf)
- System databases
A system database will create by default when the SQL server installed.
- User databases
A user database will create by the user.
SQL Server System Databases
Resource (2005 onwards)
- The Master database manages the SQL Server instance. It stores system configuration, logins details etc..
- In SQL Server 2005 onwards Master database stores only the logical information in the system objects residing in the sys schema.
- In the previous editions of SQL Server, the Master database physically stores all the system objects, (tables, Sprocs etc.) information.
- One of the main things is master and resource DB must be located in the same directory. Otherwise the server goes down 🙂
- Introduced in SQL Server 2005 to help in managing the upgrade and rollback of the system objects (service pack).
- Prior to SQL Server 2005 the system related data were stored in the master database.
- It’s a hidden and read-only database that is not accessible via SQL server management studio.
- TempDB is a temporary database to store temporary tables like #local, ##global, table variables, cursors, work tables sorted in TempDB, etc…
- When the SQL Server instance is rebooted, the TempDB database is recreated every time.
- Model is a template database for all user defined databases.
- So we could use the model as a template for other user databases (such as the recovery model, DB size, SPs etc.…) i.e. Whenever you create a new database the template will taken from the model database.
- MSDB is the main database to manage the SQL Server Agent configurations.
- It provides some of the configurations for the SQL Server Agent service (such as jobs alerts etc..) and it stores database backup details.
Important: Keep taking the system database backup regularly except tepmDB.
For More: http://msdn.microsoft.com/en-us/library/ms178028.aspx
DDL (Create, Alter, Drop,Truncate)
Truncate is DDl command or ?
yup, Truncate is DDL command .
I am already a sybase dba and want to learn sql server also.so,can you please send (or) post me some basics of sql server,architecture and difference b/w the versions.
This is muthu. I suggest you to read this post and linked book
I see that your blog is really ranking good but in case you would like your internet site to rank first on google or yahoo then U will without a doubt need link wheel backlinks.
Great blog you have here but I was wondering if you knew of any forums that cover the same topics discussed in this article? I’d really like to be a part of community where I can get feedback from other knowledgeable people that share the same interest. If you have any suggestions, please let me know. Kudos!
I’m glad you liked it. Yep there is lot of forum is available. You may ask your question in MSDN and SQL server central.
What I find so intreesitng is you could never find this anywhere else.
i’m glad it helps you.
This is Sravanthi.I completed M.Sc.Biochemistry.Recently I have done SAS-BI course for 1yr.We have SQL part in it.I am not that much profound in sql.Ur information is really useful and inspiring.Can i get further information& basics about sql.I request u.I really wish to be good in sql platform.
hi, thanks for the post.. i need a little help with the interview… i need to know “Sql skills for taking backup, run jobs, run scripts”..
thanks in advance
my email id firstname.lastname@example.org
T-SQL/MSBI Knowledge Share Videos : http://www.youtube.com/results?search_query=T+SQL/MSBI+Knowledge+Share+Videos
I am newbie to SQl Server.
I am working on the SQL Server 2008 R2 and 2012 variants, as admin.
Can you help me route a good page and Blog I can use?