Skip to main content

Posts

Showing posts from December, 2012

Excel Shortcuts

Very Useful Keyboard Shortcuts
1.To format any selected object, press ctrl+1
2.To insert current date, press ctrl+;
3.To insert current time, press ctrl+shift+;
4.To autosum selected cells, press alt + =
5.To see the suggest drop-down in a cell, press alt + down arrow
6.To enter multiple lines in a cell, press alt+enter
7.To insert a new sheet, press shift + F11
8.To edit active cell, press F2 (places cursor in the end)
9.To hide current row, press ctrl+9
10.To hide current column

Data purging and Archival from VLDB

How to delete Records from a Very Large Tables ?

If we have to delete a large records based on some conditions like year wise - month wise from very large table and our SLA is without degrading system performance then we will come into problem .We can't use TRUNCATE TABLE and we may run out of disk space and then DELETE query fails and nothing is deleted.

The Real-Problem (from SQL Bible)
A DELETE executes as a transaction where it will not commit until the last record is deleted. DELETE physically removes rows one at a time and records each deleted row in the transaction log. If the number of records in a table is small, a straight out DELETE is fine, however, with a large table the DELETE will cause the transaction log to grow. When this happens the system IO performance is degraded. Also, the table will be locked which will affect the application. The person executing the DELETE query will usually panic because what seems to be a simple task takes a long time and there is no visib…

Difference between Logins and Users

What is Difference between Logins and Users.
Logins and Users are complete different things in Microsoft SQL Server.  Many of us assumes that they are the same thing, it can get a little confusing. The basic difference is login is created on instance level and when a login is given access to a particular database then it's call a user for that database.
In simple every database user there should be a login. Logins are created at the database server instance level, while uses are created at the database level. 



CREATE LOGIN TestLogin WITH PASSWORD = 'I4india@2012' select * from sys.syslogins where name='TestLogin'
use company
CREATE USER TestUser FOR LOGIN TestLogin select * from sysusers where name='TestUser'

There are two main categories of logins: SQL Server authenticated logins and Windows authenticated logins. I will usually refer to these using the shorter names of SQL logins and Windows logins. Windows authenticated logins can either be logins mapped to Wi…

Connection and Sessions

What is the differences between Connection and Sessions in SQL-Server?
The CONNECTION is the physical communication channel between SQL Server and the applications: the TCP socket, the named pipe, the shared memory . The connections show only external connections and each connection has an spid.
selecta.spid,a.nt_username<

Database & Physical Location

Dear Friends,

Use of DMVs instead of SSMS GUI is always good sign of DBAs i understood and also trying to remove my bad habits by remembering all  DMVs,Sys Table,sys Functions & sys procedure etc.. 

On my daily activity every morning i check all database,there status and physical location using below TSQL/DMV. 

use master

SELECT     db.name AS 'DB_NAME', mf.name AS 'FILE_NAME', mf.type_desc AS 'FILE_TYPE', mf.physical_name AS 'FILE_PATH', db.state_desc,
                      db.recovery_model_desc, mf.size, mf.growth
FROM         sys.databases AS db INNER JOIN
                      sys.master_files AS mf ON db.database_id = mf.database_id




Happy Reading SumanJha_Accidental_Dba

Maximum No of Indexes Per Table

How many Indexes we can create in a Table


Friends,

This is becoming a Frequently Asked Question in SQL Server Interviews now a days. 
How many Indexes we can create in a Table ? 



Object SQL Server 7 SQL Server 2000 SQL Server 2005 SQL Server 2008 SQL Server 2012 Clustered Index 1 1 1 1 1 Non-Clustered Index 249 249 249 999 999 Total 250 250 250 1000 1000

@SumanJha