SQL Server – read only database status

This post describes how to return the database read-only status via T-SQL .

The system view – sys.databases  returns a column called is_read_only.  

The column returns either 1 or 0

 1 = read only

0 = read write

   SELECT [name] ,is_read_only  
   FROM sys.databases  
   ORDER BY [name]

 

See Also

SQL Server Database Status,monitoring and Restores

SQL Database Status with sys.databases

Arithmetic overflow error and isdate sql

Executing a t-sql statement with a convert function

 convert(datetime, my_date_column)            ,           

 threw an arithmetical overflow error with the message :

Message
 Arithmetic overflow error converting expression to data type datetime. [SQLSTATE 22003] (Error 8115)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

 

Use  the ISDATE function  , to identify invalid dates (or valid dates). Clean up as necessary.

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

select seq_number
from   dbo.STAGING_TABLE 
where  ISDATE(my_date_column ) = 0
and  my_date_column  

Author: Jack Vamvas (http://www.sqlserver-dba.com)

DATEADD and variables in SQL

To make the T-SQL  function DATEADD more dynamic use variables. The example below , accepts an input parameter , in this case “4”.

All events older than 4 months from now will be deleted.

 

DECLARE @months int
SET @months = 4

DELETE FROM [Events] WHERE [EventDate] < DATEADD (month,-1*@months, getdate())  

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Table variable and temporary table

Table variables and Temporary tables  are used interchangeably – but they were designed for different purposes.

Temporary tables were designed for storage and  manipulation of temporal data.

Table variables were designed to return User Defined Function (UDF) datasets.

 Which type should be used ?

If I estimate the data sets are smaller than 75,000 and no SQL Server indexes  are required Table variables are worth investigating.  Always test both  scenarios as part of design and acceptance testing.

Consider SQL Server test data generation testing tools   and then run some artifical queries for performance testing.

Feature comparison of Table variable and Temporary Table

Feature	Table variable	Temporary table
Constraint type	Primary Key, Unique Key , NULL	All except Foreign Key
Parallel execution plans while modifying table	No	Yes
Parallel execution plans while read-only	Yes	Yes
SELECT,INSERT,UPDATE ,DELETE	Yes	Yes
Indexes	No (except Clutsered Index)	Yes
Statistics	No	Yes
Assignment operation b/w variable	No	Yes
Affected by transaction rollbacks	No
Partitioning	No	No
Stored in sysobjects	No	Yes
Instantiated in TempDB	Yes	Yes
Altered by DDL	No	Yes
DML statement scope	1	multiple

Author: Jack Vamvas (http://www.sqlserver-dba.com)

JOIN IN SQL FAQ

1) What types of JOINS exist?

Inner Joins

Outer Joins (LEFT JOIN, LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN, FULL JOIN , FULL OUTER JOIN)

Cross Joins    (aka Cartesian Join)

2) Where are JOINS used ?

Joins are implemented in either the FROM or WHERE clause

3) Why use JOINS ?

JOINS are used to connect two tables or more based on a logical relationship

4) What are some patterns of JOIN use?

One example is  a Parent – Foreign Key relationship.

Another example is using a logical operator , such as =  or <> to define  a relationship between values in columns .

5) Are there data types where columns can’t be joined directly?

Yes.   Image, ntext , text. Although they can be joined indirectly via SUBSTRING

SELECT * FROM myTable1 JOIN myTable2 ON SUBSTRING(myTable1.textCol, 1, 10) = SUBSTRING(myTable2.textCol,1,10)

6) Can NULL values be compared?

When NULLS exist  in columns and an INNER JOIN is used , NULLS will not be matched.

Nulls can only be returned by an OUTER JOIN. If they’re excluded with the WHERE clause, then they won’t be returned

7) What is the ISO standard for INNER JOINS?

The ISO 99 Standard supports the INNER JOIN in the FROM

8) What is a Cartesian Product ?

The resultset of a CROSS JOIN without a WHERE clause.  The resultset count will be the number of rows in the first table multipled by every row in the second table.

 If 1,000,000 rows exist in the first table and 100 exist in the second the resultset count is 100,000,000

The Cartesian product is named after the Renee Descartes and is based on his work on analytic geometry

9) Can a subquery be used instead of an INNER JOIN?

It is semantically possible to use a subquery rather than an INNER JOIN. Before making the decision to use the subquery rather an INNER JOIN, analyse and report of  performance differences

 

10) Can a table JOIN to itself to create a recordset ?

 

Yes , as long as one of the table references uses a table alias. These types of JOINS are called self-join.

11) When would you use a SELF JOIN ?

Some examples are :

a)       self reference

b)       find duplicates . An alternative is ROW_NUMBER() ranking function   

Author: Jack Vamvas (http://www.sqlserver-dba.com)