SQL:Make your Database Read/Write Only

This post help you to make database read/write only,Before make read only be sure that no user is connected to that database.After confirm SP_DBOPTION will successfully set the status of Database.

1) By using ALTER TABLE
--SET DATABASE READ/WRITE ONLY
If the read-only requirements for the database are temporary, you will need to reset the configuration option following any procedures undertaken. This is achieved with a small modification to the ALTER DATABASE statement to indicate that the database should return to a writeable mode.
ALTER DATABASE Database_name SET READ_WRITE

The status read-only requirements for the database are temporary, but sometimes we need it for better administrative or Command over multiple user.
ALTER DATABASE Test_Manoj set READ_ONLY

2) By using SP_DBOPTION
EXEC SP_DBOPTION "Database_name", "READ ONLY", "TRUE"
EXEC SP_DBOPTION "Test_Manoj" ,"READ ONLY","TRUE"

The other possibility is to DENY tthe INSERT and UPDATE rights on the table that you want to set Read-Only. The Deny always override the Allow right. This way, your user will be part of the db_datawriter and you will explicitly override (and deny) the insert and update rights on some table.


Follow Link-http://javadevelopersguide.blogspot.com



By Manoj

SQL:Script for Drop All Foreign Key Constraints from Current Database

--Generate Script For Drop All Foreign Key Constraints From your Current Database
SELECT 'ALTER TABLE '+OBJECT_NAME(F.parent_object_id)+' DROP CONSTRAINT '+F.NAME FROM sys.foreign_keys F
Run the above Query.
Output-
ALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_PTCDALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_TNIDALTER TABLE COM_MST DROP CONSTRAINT FK__COM_MST__COM_TPC
Run the generated Above script
This Script will help you for Dropping all foreign key.Really, I was in trouble when my project Manager ask me for writing the query for Droping all Constraints and recreate.Really it was one of my one of the best effort to work with Database.I took the question of my project Manager and did it.


Posted By: Manoj K. Bardhan
Follow Link-http://javadevelopersguide.blogspot.com

SQL:Show Execution Plan of SQL Statement--SHOWPLAN_ALL

--show the execution plan of any statement --set Showplan_all OFF OR ShowPlan_text ON--Then Run your query check and set the Showplan to OFF
--Syntax
SET SHOWPLAN_ALL { ON OFF }
--Example
SET SHOWPLAN_ALL ON--Table is not created ,only Execution plan of the 'Create Table' type will displayCREATE TABLE TEST_TABLE(T_ID INT,T_NAME VARCHAR(50))
--Set the SHOWPLAN_ALL OFF SET SHOWPLAN_ALL OFF
--Table CreatedCREATE TABLE TEST_TABLE(T_ID INT,T_NAME VARCHAR(50))
Select Statement:
--On StatusSET SHOWPLAN_ALL ONSELECT * FROM TEST_TABLE
--Off StatusSET SHOWPLAN_ALL OFFSELECT * FROM TEST_TABLE

This Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resourcerequirements for the statements.
When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, informationabout all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table, informing users that the specifiedtable does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.SET SHOWPLAN_ALL is intended to be usedby applications written to handle its output. Use SET SHOWPLAN_TEXT to return readable output for MicrosoftWin32 command prompt applications, such as the osql utility.SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified inside a stored procedure; they must be the only statements in a batch.With SHOWPLAN_ALL statement:-
Parallel
0 = Operator is not running in parallel.(Not Sucessfully Executed)
1 = Operator is running in parallel (Sucessfully Executed)
SET SHOWPLAN_ALL returns information as a set of rows that form a hierarchical tree representing the steps taken by the SQL Server query processor as it executes each statement. Each statement reflected in the output contains a single row with the text of the statement, followed by several rows with the details of the execution steps. The table shows the columns that the output contains.Also follow SHOWPLAN_TEXT & SHOWPLAN_XML.

Posted By: Manoj K. Bardhan
Follow Link-http://javadevelopersguide.blogspot.com

SQL:FINDING ALL THE TABLE NAME & COLUMN NAME WHICH HAVING IDENTITY FIELD(AUTO INCREMENT)

--FINDING ALL THE TABLE NAME & COLUMN NAME WHICH IDENTITY IS ON(1)
SELECT C.NAME AS COLNAME,T.NAME AS TABLENAME,C.IS_IDENTITY FROM SYS.COLUMNS C INNER JOIN SYS.TABLES T ON C.OBJECT_ID=T.OBJECT_ID WHERE C.IS_IDENTITY=1


This post has so many uses for manages databases.

Posted By:-javadevelopersguide
Follow Link:-http://javadevelopersguide.blogspot.com

SQL:MAKE YOUR DATABASE OFFLINE/ONLINE

--SET YOUR DATABASE OFFLINE/ONLINE FOR DISPLAY --THIS COMMAND WILL HIDE --THE DATABASE FROM ALL USER
Setting a Database Offline/Online by using 3 way we can do.
Way 1.(By using Alter DataBase)
ALTER DATABASE SET OFFLINE
--Make ofline
ALTER DATABASE Test_Manoj SET OFFLINE
--Make online
ALTER DATABASE Test_Manoj SET OFFLINE

Way 2.(By using sp_dboptions)
--Set the Database Offline
sp_dboption database_name,'offline',true
--Set the Database Offline
sp_dboption database_name,'offline',false
Ex:-
sp_dboption Test_Manoj,'offline',true
Way 3.(By SQL Management Studio)
=>View Menu
=>Object Explorer
=>Right Click on Database
=>Task
=>Take Offline


Now , Your Database is in Offline Mode/Online Mode and enjoy the administrative command on SQL Server.

Posted By :javadevelopersguide
Follow Link-:http://javadevelopersguide.blogspot.com

SQL SERVER :Delaying Sql Execution-Use of WAITFOR Cluse

--Example:-1

--WHILE LOOP

--USE WAITFOR FOR DELAYING THE EXECUTION FOR SPECIFIED TIME

GO

DECLARE @T INT

SET @T=1

WAITFOR DELAY '00:00:10'

WHILE @T<=10

BEGIN

PRINT 'MANOJ_KUMAR'

SET @T = @T+1

END

GO

--Example:-2

GO

WAITFOR DELAY '00:00:02'

SELECT 'THIS IS DB BLOG'

GO

Descriptions:-

It block/slow down the execution Process of any sql statement(Procedure/Commands).The delay specified period of time that must pass, up to a maximum of 24 hours, before execution of a batch, stored procedure, or transaction proceeds. While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.

The actual time delay may vary from the time to time specified and depends on the activity level of the server. The time counter starts when the thread associated with the WAITFOR statement is scheduled. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time.

WAITFOR does not change the semantics of a query. If a query cannot return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified. Cursors cannot be opened on WAITFOR statements and Views cannot be defined on WAITFOR statements.

Every WAITFOR statement has a thread associated with it. If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run. SQL Server monitors the number of threads associated with WAITFOR statements, and randomly selects some of these threads to exit if the server starts to experience thread starvation.

Conclusion:- You can create a situation for Deadlock Condition.

SQL SERVER: Finding nth Highest Value

--Select 4th Highest Salary (1-way)

SELECT TOP 1 E_ID FROM (SELECT DISTINCT TOP 4 E_ID FROM EMP ORDER BY E_ID DESC)A ORDER BY E_ID ASC

--Select 4th Highest Salary (2-way)

SELECT MIN(E_ID) FROM (SELECT TOP 4 E_ID FROM EMP ORDER BY E_ID DESC)E

Description:-

This is one of the best query that I posted ,when I got an interesting question regarding this.Use of sub-queries makes complex perforemance.