Msg 1807, Level 16, State 3, Line 1 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Problem:

Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database ‘model’. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Solution:

Option 1:

            Disconnect and Reconnect your SQL Server Management Studio’s session. Your error will go away.

Option 2:

Root Cause: Root cause of this error may be one of the following reason

1. Somebody exclusively open the Model database in different session

2. If more than one create database statements attempt to run simultaneously

How to identity:

Use master 
GO
IF EXISTS(SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model'))
 PRINT 'Model Database being used by some other session'
ELSE
 PRINT 'Model Database not used by other session'

          So we can identify that whether the Model database being execlusively used by any session.., If you found anybody using the Model database execlusively, You can identify what kind of statement being used by the session...using the script given below

SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model')

         The script will return the session ID (i.e: 53)

         We have to identity what kind of query/activity being performed by this session(53).

DBCC InputBuffer(53) 

    EventInfo column returns the query performed, Based on that, you have to decide whether the Session ID(53) can be Aborted/ Not. If you want to abort the session(53),

run the command 

    Kill 53 

    Now you can try to create a new Database..!  

Renaming database and physical database files

1.Rename a Database using Management Studio:

• Renaming Database:

Right Click the Database Name in Object Explorer and Select "Rename", now you can specify new name:

However, this does not change the database file names:

SQL Query: SELECT      name, physical_name

           FROM        [SqlAndMe].sys.database_files

Result Set:

name            physical_name

--------        ----------------

MyDatabase     C:\…\MyDatabase.mdf

MyDatabase_log C:\…\MyDatabase_log.LDF

 

(2 row(s) affected)

• Renaming Files:

To change filenames, Right Click on Database in Object Explorer and Select "Properties", Then, go to "Files" Tab:

Here, you can change the logical filenames for DATA and LOG files.

2. Renaming a Database using T-SQL:

• Renaming Database:

To Rename a database using T-SQL, use below script:

 USE [master]

--Set Database to Single-User Mode

ALTER DATABASE [MyDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE




--Rename Database

ALTER DATABASE [MyDatabase] MODIFY Name = [SqlAndMe]




--Set Database to Multi-User Mode

ALTER DATABASE [SqlAndMe] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

Result Set:

The database name 'SqlAndMe' has been set.

 

•  Renaming Files:

--Rename Logical File Names

ALTER DATABASE [SqlAndMe]

            MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

ALTER DATABASE [SqlAndMe]

           MODIFY FILE (NAME=N'MyDatabase_log',NEWNAME=N'SqlAndMe_log')

Result Set:

The file name 'SqlAndMe' has been set.

The file name 'SqlAndMe_log' has been set.

 

However, none of these two methods can change the physical database file name, you can check this using sys.database_files:

SQL Query:SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

 

Result Set:

name          physical_name

——---        —————------------

SqlAndMe      C:\…\MyDatabase.mdf

SqlAndMe_log  C:\…\MyDatabase_log.LDF


  

(2 row(s) affected)

 

Sometimes we also need to rename the physical database files, this cannot be done via two methods described above.

3. Renaming a Database using detach/attach:


1. Detach database:
You can detach a database either using Management Studio or T-SQL, To detach a database using Management Studio, right-click on database > go to "Tasks" > "Detach", click OK to detach a database,
To detach a database using T-SQL, you can use master..sp_detach_db procedure:

--Detach Database using sp_detach_db

SQL Query:USE [master]

          ALTER DATABASE [MyDatabase]

          SET SINGLE_USER WITH ROLLBACK IMMEDIATE




        EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

 2.Rename Physical files:
Once the database is detached, SQL Server releases the locks on physical files, now you can rename the physical files using Windows Explorer, or command prompt:

 

 

 3. Attaching database with New Name:
Once, the physical files have been renamed, you can attach the database using new database and file names:

-– Attach Database

SQL Query:USE [master]

          CREATE DATABASE [SqlAndMe] ON

          ( FILENAME = N'C:\…\SqlAndMe.mdf'),

          ( FILENAME = N'C:\…\SqlAndMe_log.LDF')

          FOR ATTACH

 

--Rename Logical file names

SQL Query: USE [SqlAndMe]

           ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

          ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase_log', NEWNAME=N'SqlAndMe_log')




SQL Query: SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name          physical_name

————  —————–

SqlAndMe      C:\…\SqlAndMe.mdf

SqlAndMe_log  C:\…\SqlAndMe_log.LDF


 

(2 row(s) affected)

SPACE function will return maximum of 8000 spaces only

• We can use SPACE function to replicate spaces for a string. Consider the following statement 

 SQL Query: select 'A'+space(10)+'B'
The result is:  A          B

• As you see space function adds 10 spaces between the string A and B. Note that the space function though accepts any positive number, it will return maximum of 8000 spaces only which you can confirm from the following example  

SQL Query: select datalength(space(10000))

• The above returns 8000. So you should be aware of this. The alternate solution to have more spaces is to use replicate function as shown below

SQL Query: select datalength(replicate(cast(' ' as varchar(max)),10000))  

• Which returns 10000. Also note that replicate returns maximum 8000 characters so we need to convert the string to varchar(max) datatype to have more chanracters. 

• Varchar(max) datatype and Replicate function

  SQL Query: declare @v varchar(max)
             set @v=replicate('a',50000)
             select len(@v),datalength(@v)
  • Note that the result is not 50000 but 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype

  To get a correct result, you need to convert the expression to the type of varchar(max)

  SQL Query:declare @v varchar(max)

            set @v=replicate(convert(varchar(max),'a'),50000)

            select len(@v),datalength(@v)

  • Now the result is 50000 as expected.

  So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datat

   

   

Deterministic Functions and Nondeterministic Functions

Deterministic functions: always returns the same output result all the time it is executed for same input values.

             Exp: ABS, DATEDIFF, ISNULL etc.

Nondeterministic functions: may return different results each time they are executed. 

             Exp: NEWID, RAND, @@CPU_BUSY etc. 

Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.

Oracle DataTypes

Data types for Oracle 8 - Oracle 11g + PL/SQL

Datatype	Description	Max Size: Oracle 8	Max Size: Oracle 9i/10g	Max Size: Oracle 11g	Max Size: PL/SQL	PL/SQL Subtypes/ Synonyms
VARCHAR2(size)	Variable length character string having maximum lengthsize bytes. You must specify size	4000 bytes minimum is 1	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
NVARCHAR2(size)	Variable length national character set string having maximum length size bytes. You must specify size	4000 bytes minimum is 1	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
VARCHAR	Now deprecated (provided for backward compatibility only) VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.	-	-	-
CHAR(size)	Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 bytes Default and minimum size is 1 byte.	CHARACTER
NCHAR(size)	Fixed length national character set data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 bytes Default and minimum size is 1 byte.
NUMBER(p,s)	Number having precision p and scale s.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	Magnitude 1E-130 .. 10E125 maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits The scale s can range from -84 to 127. For floating point don't specify p,s REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits	fixed-point numbers: DEC DECIMAL NUMERIC floating-point: DOUBLE PRECISION FLOAT binary_float (32 bit) binary_double (64 bit) integers: INTEGER INT SMALLINT simple_integer(10g) BOOLEAN REAL
PLS_INTEGER	signed integers PLS_INTEGER values require less storage and provide better performance than NUMBER values. So use PLS_INTEGER where you can!	PL/SQL only	PL/SQL only	PL/SQL only	magnitude range is -2147483647 .. 2147483647
BINARY_INTEGER	signed integers (older slower version of PLS_INTEGER)				magnitude range is -2147483647 .. 2147483647	NATURAL NATURALN POSITIVE POSITIVEN SIGNTYPE
LONG	Character data of variable length (A bigger version the VARCHAR2 datatype)	2 Gigabytes	2 Gigabytes - but now deprecated (provided for backward compatibility only).	2 Gigabytes - but now deprecated (provided for backward compatibility only).	32760 bytes Note this is smalller than the maximum width of a LONG column
DATE	Valid date range	from January 1, 4712 BC to December 31, 9999AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31,9999 AD. (in Oracle7 = 4712 AD)
TIMESTAMP (fractional_seconds_precision)	the number of digits in the fractional part of the SECOND datetime field.	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE	As above with time zone displacement value	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
INTERVAL YEAR (year_precision) TO MONTH	Time in years and months, where year_precision is the number of digits in the YEAR datetime field.	-	Accepted values are 0 to 9. (default = 2)	Accepted values are 0 to 9. (default = 2)
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)	Time in days, hours, minutes, and seconds. day_precision is the maximum number of digits in 'DAY' fractional_seconds_precisionis the max number of fractional digits in the SECOND field.	-	day_precision may be 0 to 9. (default = 2) fractional_seconds_precisionmay be 0 to 9. (default = 6)	day_precision may be 0 to 9. (default = 2) fractional_seconds_precisionmay be 0 to 9. (default = 6)
RAW(size)	Raw binary data of length size bytes. You must specify size for a RAW value.	Maximum size is 2000bytes	Maximum size is 2000 bytes	Maximum size is 2000 bytes	32767 bytes
LONG RAW	Raw binary data of variable length. (not intrepreted by PL/SQL)	2 Gigabytes.	2 Gigabytes - but now deprecated (provided for backward compatibility only)	2 Gigabytes - but now deprecated (provided for backward compatibility only)	32760 bytes Note this is smalller than the maximum width of a LONG RAW column
ROWID	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)	10 bytes	10 bytes	10 bytes	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)
UROWID	Hex string representing the logical address of a row of an index-organized table	The maximum size and default is 4000 bytes	The maximum size and default is 4000 bytes	The maximum size and default is 4000 bytes	universal rowid - Hex string representing the logical address of a row of an index-organized table, either physical, logical, or foreign (non-Oracle)	SeeCHARTOROWIDand the package:DBMS_ROWID
MLSLABEL	Binary format of an operating system label.This datatype is used with Trusted Oracle7.
CLOB	Character Large Object	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
NCLOB	National Character Large Object	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BLOB	Binary Large Object	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BFILE	pointer to binary file on disk	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
XMLType	XML data	-	4 Gigabytes	4 Gigabytes	Populate with XML from a CLOB or VARCHAR2. or query from another XMLType column.

Notes and Examples

VARCHAR2:
Storing character data as Varchar2 will save space:

Store 'SMITH' not 'SMITH     '

Oracle9i and above allow Varchar2 columns to be defined as a number of bytes VARCHAR2(50 BYTE) or a number of characters VARCHAR2(50 CHAR), the latter is useful if the database is ever converted to run a double-byte character set (such as Japanese), you won't have to edit the column sizes. The default measure, normally BYTE, is set withnls_length_semantics.

CHAR:
Over time, when varchar2 columns are updated they will sometimes create chained rows, CHAR columns are fixed width they are not affected by this, so less DBA effort is required to maintain performance.

PL/SQL
When retrieving data for a NUMBER column, consider using the PL/SQL datatype: PLS_INTEGER for better performance.

LONG
Use BLOB instead of LONG

INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)

The FLOAT datatype
This ANSI datatype will be accepted by Oracle - Very similar to NUMBER it stores zero, positive, and negative floating-point numbers

The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers

Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point. 

Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)

Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38

Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to convert)

{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}

Example

 The value 7,456,123.89 will display as follows
NUMBER(9)     7456124 
NUMBER(9,1)   7456123.9
NUMBER(*,1)   7456123.9    
NUMBER(9,2)   7456123.89 
NUMBER(6)    [not accepted exceeds precision] 
NUMBER(7,-2)  7456100 
NUMBER        7456123.89 
FLOAT         7456123.89 
FLOAT(12)     7456000.0

Storing Varchar2 Data

For VARCHAR2 variable whose maximum size is less than 2,000 bytes (or for a CHAR variable), PL/SQL allocates enough memory for the maximum size at compile time.

For a VARCHAR2 whose maximum size is 2,000 bytes or more, PL/SQL allocates enough memory to store the actual value at run time. In this way, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.

For example, if you assign the same 500-byte value to VARCHAR2(1999 BYTE) and VARCHAR2(2000 BYTE) variables, PL/SQL allocates 1999 bytes for the former variable at compile time and 500 bytes for the latter variable at run time.

Storing Numeric Data

Oracle stores all numeric data in variable length format - storage space is therefore dependent on the length of all the individual values stored in the table. Precision and scale settings do not affect storage requirements. DATA_SCALE may appear to be truncating data, but Oracle still stores the exact values as input. DATA_PRECISION can be used to constrain input values.

It is possible to save storage space by having an application truncate a fractional value before inserting into a table, but you have to be very sure the business logic makes sense.

Select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
From cols Where table_name = 'Your_Table';

A common space-saving trick is storing boolean values as an Oracle CHAR, rather than NUMBER:

Create TABLE my_demo (accountcode NUMBER, postableYN CHAR check (postableYN in (0,1)) );

-- Standard Boolean values: False=0 and True=1
Insert into my_demo values(525, '1');
Insert into my_demo values(526, '0');

Select accountcode, decode(postableYN,1,'True',0,'False') FROM my_demo;
-- or in French:
Select accountcode, decode(postableYN,1,'Vrai',0,'Faux') FROM my_demo;

Comparison with other RDBMS's

	int10	int6	int1	char(n)	blob	XML
Oracle 11	NUMBER(10)	NUMBER(6)	NUMBER(1)	VARCHAR2(n)	BLOB	XMLType
MS SQL Server 2005	NUMERIC(10)	NUMERIC(6)	TINYINT	VARCHAR(n)	IMAGE	XML
Sybase system 10	NUMERIC(10)	NUMERIC(6)	NUMERIC(1)	VARCHAR(n)	IMAGE
MS Access (Jet)	Long Int or Double	Single	Byte	TEXT(n)	LONGBINARY
TERADATA	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARBYTE(20480)
DB2	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARCHAR(255)
RDB	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	LONG VARCHAR
INFORMIX	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	BYTE
RedBrick	integer	int	int	char(n)	char(1024)
INGRES	INTEGER	INTEGER	INTEGER	VARCHAR(n)	VARCHAR(1500)

Also consider the maximum length of a table name (or column name) and the maximum size of an SQL statement - these limits vary considerably between products and versions.

Converts an int ASCII code to a character.

Char will convert an int ASCII code to a character. 

Syntax 

CHAR ( integer_expression ) 

Integer_expression

            Integer_expression is an integer from 0 through 255. NULL is returned if the integer expression is not in this range. 

Example  

Char(1) . . . . . . . Char(255)  

               CHAR can be used to insert control characters into character string. The following table shows some frequently used control characters.

Control character	value
Tab	Char(9)
Line feed	Char(10)
Carriage return	Char(13)

CREATE TABLE #Temp (id INT IDENTITY,symd CHAR(10))

DECLARE @I INT =1

WHILE (@I<=255)

BEGIN

INSERT INTO #Temp SELECT CHAR(@I)

SET @I =@I+1

END

SELECT id 'Char Value',symd 'Control character'  FROM #Temp

DROP TABLE #Temp

Constructing row values into comma-separate string

 ---Create Table

CREATE TABLE #USER (USERNAME VARCHAR(25))

---Inserting Values

INSERT INTO #USER (USERNAME) VALUES ('Loki')

INSERT INTO #USER (USERNAME) VALUES ('Chinnu')

INSERT INTO #USER (USERNAME) VALUES ('Chinni')

INSERT INTO #USER (USERNAME) VALUES ('Laddu')

---Constricting comma-separate string

SELECT DISTINCT STUFF((SELECT ',' +USERNAME  FROM #user  FOR XML PATH ('')),1,1,'')AS LIST FROM #user

--- Drop table

DROP TABLE #USER

Finding Nth row

--To get the nth row value

 

Select Type, Price from

(

Select TYPE, Price, RowNum , (z.RowNum % 5) as RowMod from

(

Select TYPE, Price, RowNum from

(

Select *, Row_Number() Over(Order By Model ASC) as RowNum from

(Select a.Model, a.TYPE, b.Price from Product a Left Join

(Select distinct p.Model, Avg(Price) Price from Product p inner join PC pc 

on p.Model=pc.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from  Product p inner join Laptop l

on p.Model=l.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from  Product p inner join Printer pr

on p.Model=pr.model Group by p.Model

) b on a.Model=b.Model

) x

) y 

) z

) FinalTable

Where RowMod=0

--------------------------------------------------------------------------

--To get the nth row value

Select TYPE, Price from

(

Select *, Row_Number() Over(Order By Model ASC) as RowNum from

(Select a.Model, a.TYPE, b.Price from Product a Left Join

(Select distinct p.Model, Avg(Price) Price from Product p inner join PC pc 

on p.Model=pc.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from  Product p inner join Laptop l

on p.Model=l.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from  Product p inner join Printer pr

on p.Model=pr.model Group by p.Model

) b on a.Model=b.Model

) x

) z  where RowNum=5