Database isolation level quick note

• Reference: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

Isolation Levels, Read Phenomena and Locks

[edit] Isolation Levels vs Read Phenomena

Isolation level Dirty reads Non-repeatable reads Phantoms Read Uncommitted may occur may occur may occur Read Committed - may occur may occur Repeatable Read - - may occur Serializable - - -

"may occur" means that the isolation level suffers that phenomenon, while "-" means that it does not suffer it.

[edit] Isolation Levels vs Locks

Isolation level	Write Lock	Read Lock	Range Lock
Read Uncommitted	-	-	-
Read Committed	V	-	-
Repeatable Read	V	V	-
Serializable	V	V	V

"V" indicates that the method locks for that operation, keeping that lock till the end of the transaction containing that operation.
Note: Read (i.e. SELECT) operations can acquire read (shared) locks in the Read Committed isolation level, but they are released immediately after the read operation is performed.

• Reference: http://msdn.microsoft.com/en-us/library/ms173763.aspx 
• Sql server 2008 isolation level:

  SET TRANSACTION ISOLATION LEVEL
      { READ UNCOMMITTED
      | READ COMMITTED
      | REPEATABLE READ
      | SNAPSHOT
      | SERIALIZABLE
      }
  [ ; ]

  SERIALIZABLE

  Specifies the following:
  

  • (READ COMMITTED) no dirty read
    Statements cannot read data that has been modified but not yet committed by other transactions.
    
  • (REPEATABLE READ)
    No other transactions can modify data that has been read by the current transaction until the current transaction completes.
    
  • (No Phantom Read) Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
    

  Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction.

ACID (atomicity, consistency, isolation, durability)

Finding Sybase ASE database page size

1) t-sql



select @@pagesize, @@maxpagesize
2) t-sql

select pagesize("master..sysobjects")




3) t-sql (works across all Sybase version)

select

v.low as 'pagesize'
from master.dbo.spt_values v
where v.number = 1
and v.type = "E"

4) check database log file:


 grep "pagesize" logfile.txt

No default configure value for Sybase?

One easy to know what is your sybase database server instance's configuration value which is NOT same as the default is to run this query:
sp_configure 'display'

string splitter with t-sql (sql server and sybase)

The following function for sql server should give you an idea of string splitting into a table without looping. The idea should work with both sql server and sybase.

CREATE FUNCTION [dbo].[SplitString](@arr AS VARCHAR(8000), @sep AS CHAR(1))

RETURNS TABLE

AS

RETURN

SELECT

(number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1 AS pos,

LTRIM(RTRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number))) AS element

FROM (select number from master..spt_values where type='p') as Numbers

WHERE number <= LEN(@arr) + 1

AND SUBSTRING(@sep + @arr, number, 1) = @sep;

Reset sa password for MS sql server and Sybase ASE

It may happen that you may not be able  to access some running sql server at all. You may forget to assign administrators to sql server as administrator or you forget sa password.

What you need is to have at least one account with windows administrator' privilege and you can add any windows account access to sql server. Please note that windows authentication is always possible, so you may just assign admin right for sql server with any windows account and reset sa password if necessary.

Before you re-install the sql server, just follow these steps.

Assumption:

•     sql server installation is complete and is sql server service is running correctly.

•     Forgot sa password, your known window account does not have permission to connect to sql server.

•     Have a window account which has admin right, or at least some one can give you one account with this privilege.

Here are the steps to take

•     step 1: start sql server in minimum configuration mode. For example:

            for default instance
        cd  "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\"
        or for SQL2008 instance
        cd "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\"
        sqlservr.exe -f (or sqlservr.ex -m)
        Note: make sure you "Run as administer" from DOS!
        Have a window account which has admin right, or at least some one can give you one account with this privilege.

•    step 2: connect to sql server (using the same account which started the sql server from step 1)

        list all sql server running "near you" or "at the windows network"
        sqlcmd -L
        sqlcmd -E -S MSSQLSERVER (or the correct sql server instance name)

•    step 3: add your windows account or build in admin account as sql server administrator

               CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
                GO
                EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
                GO

•         step 4: configure all logins and remove logins which are not necessary any more.

P.S.
            Different way to start up sql server manually.
            1) net start:
        -- default instance name/service name for sql server
        -- net stop mssqlserver
        -- net start mssqlserver
      2) using sqlservr.exe
    
      reference:


        /**
        method 1
        http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/
        adding -m; in start parameter
        */

        /* method 2
        (sqlcmd is better than osql)
        find the list of sql server instance
        sqlcmd -L
        -- connect
        sqlcmd -E -S sql_server_instance
        */

                change sa password with t-sql:
        USE [master]
        GO
        ALTER LOGIN [sa] WITH PASSWORD=N''
        GO
        -- this will be removed in future version
        sp_password @old=NULL, @new=N'', @loginame=N'sa'
        go

You may think of change sql server login mode, but you will always have window authentication anyway.
But in case you want to change the security mode after installation is to stop SQL Server and set the appropriate registry key for your installation:

(from MSDN, this seems for sql server 2000)

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
to 0 or 2 for mixed-mode or 1 for integrated. (Integrated is the default setup for the SQL Server 2000 Data Engine.)

Note The 0 and 2 values have the same functionality. They both set the security to mixed-mode.

Another note, to reset sa password for Sybase ASE, you can start up server with some optional.

http://www.sybaseteam.com/recover-sa-password-sybase-ase-step-by-step-t-933.html

The following is copied from the url above:
Recover sa password in Sybase ASE - Step by Step process

Recover sa password in Sybase ASE - Step by Step process

If the sa password is lost, the step by step process mentioned below can be followed to recover the ‘sa’ password.

Step 1. Shutdown the server - kill the processes at OS level as it is not possible to shutdown the server
Step 2. Make a copy of RUN_ file say. RUN__backup
Step 3. Edit the RUN_ file to include an argument -psa . Including "-psa" to the RUN file will generate a new password for the 'sa' login. After editing , the RUN_ file looks as below.

----------------------------------------------------------------------------------
$cat RUN_TEST_SYBASE15
#!/bin/sh
#
# ASE page size (KB): 2k
# Master device path: /usr/local/sybase15/sybdev/data/master.dat
# Error log path: /usr/local/sybase15/ASE-15_0/install/TEST_SYBASE15.log
# Configuration file path: /usr/local/sybase15/ASE-15_0/TEST_SYBASE15.cfg
# Directory for shared memory files: /usr/local/sybase15/ASE-15_0
# Adaptive Server name: TEST_SYBASE15
#
/usr/local/sybase15/ASE-15_0/bin/dataserver \
-d/usr/local/sybase15/sybdev/data/master.dat \
-e/usr/local/sybase15/ASE-15_0/install/TEST_SYBASE15.log \
-c/usr/local/sybase15/ASE-15_0/TEST_SYBASE15.cfg \
-M/usr/local/sybase15/ASE-15_0 \
-sTEST_SYBASE15 \
-psa \

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

Step 4. Start the Sybase server using the command startserver.The password will be printed out on the console as shown below( please note that the sa password will not be written into error log) .Look for it on the console.

$startserver -f RUN_TEST_SYBASE15
00:00000:00000:2010/05/05 16:13:55.69 kernel SySAM: Checked out license for 1 ASE_CORE (2009.1231/permanent/19B3 47BC 0B7E 8DC3).
.............................................................
.............................................................
00:00000:00001:2010/05/05 16:14:01.28 server Completed REDO pass for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.28 server Recovery of database 'sybsystemprocs' will undo incomplete nested top actions.
00:00000:00001:2010/05/05 16:14:01.28 server Started recovery checkpoint for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.30 server Completed recovery checkpoint for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.32 server Started filling free space info for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.34 server Completed filling free space info for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.36 server Started cleaning up the default data cache for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.36 server Completed cleaning up the default data cache for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.37 server Checking external objects.
00:00000:00001:2010/05/05 16:14:01.43 server The transaction log in the database 'sybsystemprocs' will use I/O size of 2 Kb.
00:00000:00001:2010/05/05 16:14:01.44 server Database 'sybsystemprocs' is now online.

New SSO password for sa:yjyhzychbtafk8

00:00000:00008:2010/05/05 16:14:01.48 kernel.......................................
00:00000:00008:2010/05/05 16:14:01.48 kernel.......................................
----------------------------------------------------------------------------------------

Step 5. Login as sa user using the above password.
Step 6. Change the sa password using sp_password command.

$isql -Usa -w132 -c -Pyjyhzychbtafk8
1> sp_password yjyhzychbtafk8,qwerty4321
2>
Password correctly set.

(return status = 0)
1>

Step 7. Remove the -psa argument in RUN_ file. / Copy the RUN__backup file to RUN_ file.

Different way to get sql server instance name

--1.
SELECT SERVERPROPERTY('InstanceName')

--2
sp_helpserver

--3
select @@SERVERNAME

--4
SELECT * FROM SYS.SYSSERVERS

--5
SELECT * FROM SYS.SERVERS

--6
select @@servicename

normalization definition

Table 1. Data Normalization Rules.

Level	Rule
First normal form (1NF)	2 conditions: An entity type is in 1NF when  (A)  it contains no repeating groups of data. (This is typically already implied in sql server, unless you have varchar type to hold an array of data, say "blot/screw/ for product field, which represents all products for a specified store. Also xml data type could hold array of data) And  (B) it is not allowed to have duplicated rows. (this can be done easily with a primary key of a table). It has to have a primary key, otherwise it will allow duplicate data, which violates 1NF. Also repeated data comes with mixing up 2 entities into 1 tables, such as AccountOrder type table. or Inventory table where both product and store information is stored in this table.
Second normal form (2NF)	An entity type is in 2NF when it is in 1NF and when all of its non-key attributes are fully dependent on its primary key.  That is, partial dependance (for example, depend on only one of composed primary key field) is not allowed. Typically dimensional tables in OLAP database only normalized to 2ND. However, fact tables are generally normalized to 3NF.
Third normal form (3NF)	An entity type is in 3NF when it is in 2NF and when all of its attributes are directly dependent on the primary key. That is, non-key attributes depend ONLY (Solely) on primary key, nothing else. If Store table contains Product column. Since product does depend on store since it specifies the product is manufactured by that store. However product does not depend ONLY on store. So this is 2NF, not 3NF table. To make a 3NF table, we need to create a new table Product.