Category Archives: MSSQLServer

Indexed View in Sql Server

Indexed View

A view with a unique clustered index is an indexed view. It will save data for better performance and utilized the index on the query. It help SQL Server to increase performance. It act like materialized view in oracle.

As base table is updated the indexed view is also updated.

View: A view is the normal representation of SQL queries. It is saved in data dictionary of the database. It is used to keep the complex query for reuse purpose, security purpose( if not want to represent the exact structure of the database to real world).

View does not have its own data it will fetch data from its base tables.

Example for creating the Index view:

1. Create view on the SQL Query:

CREATE VIEW index_view_name AS
SELECT columnA, columnB FROM dbo.table_name
GO

 

2. Create unique clustered index on the view:

CREATE UNIQUE CLUSTERED INDEX idx_index_view_name ON index_view_name(columnA)
GO

 

Advertisements

Enable the xp cmdshell in Sql Server

XP_CMDSHELL is the extended stored procedure which is used to execute the Operating System command from the SQL Server TSQL language. It is stored in Master database.

Example:

exec master.dbo.xp_cmdshell 'dir D:\backup\*.bak'

exec master.dbo.xp_cmdshell 'mkdir "D:\backup\29dec2013"'

 

Steps for Enable the XP_CMDSHELL 

1. Go to the SQL Server Management Studio

2. Connect with instance with super user (sa user)

3. Use the master database

4. Execute the following command for enable xp_cmdshell

Use Master
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE WITH OVERRIDE
GO

 

Steps for disable the XP_CMDSHELL

1. Go to the SQL Server Management Studio

2. Connect with instance with super user (sa user)

3. Use the master database

4. Execute the following command for enable xp_cmdshell

Use Master
GO
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 0
RECONFIGURE WITH OVERRIDE>
GO

EXEC master.dbo.sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO

 

Following error occured on execution of xp_cmdshell if it is disabled:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

Check the size of database and table in Microsoft SQL Server

Check the size of Database and table in Microsoft SQL Server

Check the Size of Database in SSQL Server

use "ADVENTUREWORKS"
exec sp_spaceused

Output:

database_name   database_size   unallocated space
adventureworks  17899.13 MB     5309.39 MB


Find the Size of Database files in Microsoft SQL Server

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) like '%AdventureWorks%'
order by SizeMB desc

Output:

DatabaseName    Logical_Name       Physical_Name                    SizeMB
ADVENTUREWORKS  ADVENTUREWORKS     D:\MSSQL\Data\adventure.mdf      21453           AVENTUREWORKS   ADVENTUREWORKS_log D:\MSSQL\Data\adventure.ldf      5234


Check the size of database

sp_helpdb'adventureworks'

Check size of data files in database

USE AdventureWorks;
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;

Check Size of Table

sp_spaceused 'MyTable'

Truncate SQL Server Transaction Log

Truncate SQL Server Transaction Log file

1. Change the recovery mode from full to simple

ALTER DATABASE dbname SET RECOVERY SIMPLE;

 
2. By using dbcc commands shrink the transaction log file

DBCC SHRINKFILE (@dbfilename , 1024)

 
3. Change back the recovery mode of database to full.

ALTER DATABASE dbname SET RECOVERY FULL;

 
4.  Took full backup of database
 
Note: If log shipping or database mirroring is establish please do not following the above method.

1. Take full backup of database and log file

2. Shrink the transactional log file of the database:

dbcc shrinkfile(dbfilename,1024);

 

Rename table and column name for Oracle, SQL Server, Mysql & POSTGRESQL

Rename table and column name for Oracle, SQL Server, Mysql & POSTGRESQL

Following command for rename the Table Name

SQL Server

EXEC sp_rename 'oldtablename', 'newtablename';

Oracle

alter table tablename rename to  newtablename;

MYSQL

RENAME TABLE table_name TO new_table_name;

POSTGRESQL

ALTER TABLE table_name RENAME TO newtablename;

Following command for Rename Column Name in table

SQL Server

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

Oracle

alter table tablename columname rename to newcolumname

MYSQL

alter table change oldcoumnname newcoklumnaate

POSTGRESQL

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

connect to SQL Server if administrator user is deleted and sa is disabled

connect to SQL Server if administrator user is deleted and sa is disabled

1. Stop the SQL Server and SQL Server Agent services in Services.msc

Go to Start –> Run –> services.msc

Stop the SQL Server and SQL Server Agent Service

2. Open a cmd prompt window

Go to Start –> Run –> cmd and press the ‘OK’ button
change directory to SQL Server’s Binn directory as shown below.

C:\MSSQL2008\MSSQLSERVER\MSSQL\Binn>

3. start the sql server in single user mode (sqlservr -m)

C:\MSSQL2008\MSSQLSERVER\MSSQL\Binn> sqlservr -m

wait for 5 minutes

4. Connected with window system administrator

START –> RUN –> cmd

C:\> sqlcmd -E

-E option is used as trusted connection

5. create login with sysadmin priviliges :

create logins mssql with password=’mssql’;
go
exec sp_addsrvrolemember [mssql],[sysadmin]
go

6. Start sql server management studio and connect with new created logins

7. Test permission with
sp_helpsrvrolemember sysadmin