SQL Server Delete Cascade

src - http://randomconsultant.blogspot.in/2008/10/sql-server-cascade-delete.html

Delete cascade , recursive delete sqlserver 

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure. 

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
*/
 (
 @cTableName varchar(50), /* name of the table where rows are to be deleted */
 @cCriteria nvarchar(1000), /* criteria used to delete the rows required */
 @iRowsAffected int OUTPUT /* number of records affected by the delete */
 )
As
set nocount on
declare  @cTab varchar(255), /* name of the child table */
 @cCol varchar(255), /* name of the linking field on the child table */
 @cRefTab varchar(255), /* name of the parent table */
 @cRefCol varchar(255), /* name of the linking field in the parent table */
 @cFKName varchar(255), /* name of the foreign key */
 @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
 @cChildCriteria nvarchar(1000), /* criteria to be used to delete 
                                           records from the child table */
 @iChildRows int /* number of rows deleted from the child table */

/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR 
SELECT SO1.name AS Tab, 
       SC1.name AS Col, 
       SO2.name AS RefTab, 
       SC2.name AS RefCol, 
       FO.name AS FKName
FROM dbo.sysforeignkeys FK  
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
                              AND FK.fkey = SC1.colid 
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
                              AND FK.rkey = SC2.colid 
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
     BEGIN
 /* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
 SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
                              @cRefTab +'] WHERE ' + @cCriteria + ')'
 print 'Deleting records from table ' + @cTab
 /* call this procedure to delete the child rows */
 EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
 FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
     END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected  */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName

In the above scenario, we were trying to perform the following :

    DELETE FROM X WHERE field1 = '234'

Using this procedure, we would use the following command:

    exec spDeleteRows 'X', 'field1 = ''234''', 0

Hashbytes sqlserver

HASHBYTES(), as the name implies, is a function or algorithm that generates a hash from some input. If you're not familiar with what a hash is, here's a good working definition for a hash function:
hash function - A hash function takes in data and returns back a fixed length block of bits such that any change to the data should result in a different block.

HASHBYTES() is actually a function which provides access to several hashing algorithms. In the earlier versions, it supports MD2, MD4, MD5, SHA, SHA1 algorithms and these algorithms are limited up to 20 bytes only.
In SQL Server 2012, we have an enhancement in this function and now it supports SHA2_256, SHA2_512 algorithms that can generate 32 and 64 bytes hash codes for the respective input.
SQL Server 2005 and up have the following protocols (how you specify them in HASHBYTES is in parentheses):

    MD 2     (MD2)
    MD 4     (MD4)
    MD 5     (MD5)
    SHA-0   (SHA)
    SHA-1   (SHA1)

SQL Server 2012 introduces these additional hashing algorithms:
    SHA-2 256 bits AKA SHA-256 (SHA2_256)
    SHA-2 512 bits AKA SHA-512 (SHA2_512)

Hashing Algorithms You Should Probably Avoid
Since a hash function will return a fixed length block, obviously, there are a finite set of possibilities for the output. Therefore, there's bound to be different inputs that will result in the same output. We call these situations collisions. What is of concern is if a collision can be engineered fairly quickly/cheaply. This is referred to as a collision attack. If a hashing algorithm is susceptible to this type of attack or other attacks with reasonable resources that either (a) allow you to create an identical hash with different input or (b) figure out the input from the hash, then those algorithms should be avoided. As of the writing of this tip, the following algorithms fall into that list:
    MD 2 - developed in 1989 by Ronald Rivest, it generates a 128-bit hash value. It is susceptible to several attacks and is no longer considered cryptopgraphically secure.
    MD 4 - Also developed by Rivest (1990), it generates a 128-bit hash value like MD 2. And like MD 2, it is susceptible to a couple of attacks and is no longer considered cryptographically secure.
    MD 5 - Developed by Rivest in 1992, it also generates a 128-bit hash value. While we see it used often to "digitally fingerprint" files and documents, it is also considered "broken" and no longer cryptographically secure.
    SHA-0 - SHA-0 was withdrawn shortly after being made public due to a "significant" flaw. It generates a 160-bit hash value. It was replaced by SHA-1.
Hashing Algorithms Which Are Good for Now
If you're not on SQL Server 2012, SHA-1 is the best choice to use.

The SHA-2 algorithm comes in several block sizes, of which SQL Server 2012 implements two:
    SHA-2 256 bit block size (called SHA-256)
    SHA-2 512 bit block size (called SHA-512)


Some simple examples
 :
DECLARE @String varchar(7);
Set @String ='j4info'

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD2' AS [Algorithm type]
, HASHBYTES('MD2', @String) as [HashBytes]
, LEN(HASHBYTES('MD2', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD4' AS [Algorithm type]
, HASHBYTES('MD4', @String) as [HashBytes]
, LEN(HASHBYTES('MD4', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD5' AS [Algorithm type]
, HASHBYTES('MD5', @String) as [HashBytes]
, LEN(HASHBYTES('MD5', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA' AS [Algorithm type]
, HASHBYTES('SHA', @String) as [HashBytes]
, LEN(HASHBYTES('SHA', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA1' AS [Algorithm type]
, HASHBYTES('SHA1', @String) as [HashBytes]
, LEN(HASHBYTES('SHA1', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_256' AS [Algorithm type]
, HASHBYTES('SHA2_256', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_256', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_512' AS [Algorithm type]
, HASHBYTES('SHA2_512', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_512', @String)) as [Length in Bytes]
GO
Note : Executions of above script in earlier version of SQL Server, it will return NULL value for SHA2_256 & SHA2_512.

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Grant required role/Previliges to user:
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;

Create storage preferences and print joins (lexer - basic_lexer)

begin
    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k) lob (data) store as (disable storage in row cache)');
    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE','tablespace LOB_INDEX storage (initial 32k) compress 2');
    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
    ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
    ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
    ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES'); 
end;
/
CREATE TABLE my_docs (
    id    NUMBER(10)     NOT NULL,
    name  VARCHAR2(200)  NOT NULL,
    doc   BLOB           NOT NULL
);

create index domain_idx on my_docs(doc) indextype is ctxsys.context parameters( 'lexer mylex storage mystore');
Now index data will store in tablespace "LOB_INDEX"


YOU may also like :-
basic-lexer printjoins domain indexes

Lexer -> Basic Lexer -> Printjoins in DOMAIN index

lexer  Web definitions

Fancy term for a tokener..

Lexer Types

Use the lexer preference to specify the language of the text to be indexed. To create a lexer preference, you must use one of the following lexer types:

Type     Description
BASIC_LEXER                     Lexer for extracting tokens from text in languages, such as English and most western European languages that use white space delimited words.
MULTI_LEXER                    Lexer for indexing tables containing documents of different languages
CHINESE_VGRAM_LEXER     Lexer for extracting tokens from Chinese text.
CHINESE_LEXER                 Lexer for extracting tokens from Chinese text.
JAPANESE_VGRAM_LEXER   Lexer for extracting tokens from Japanese text.
JAPANESE_LEXER               Lexer for extracting tokens from Japanese text.
KOREAN_MORPH_LEXER     Lexer for extracting tokens from Korean text.
USER_LEXER                      Lexer you create to index a particular language.
WORLD_LEXER                   Lexer for indexing tables containing documents of different languages; autodetects languages in a document


BASIC_LEXER

Use the BASIC_LEXER type to identify tokens for creating Text indexes for English and all other
supported whitespace-delimited languages.

The BASIC_LEXER also enables base-letter conversion, composite word indexing, case-sensitive indexing
 and alternate spelling for whitespace-delimited languages that have extended character sets.

In English and French, you can use the BASIC_LEXER to enable theme indexing.

Note:
Any processing the lexer does to tokens before indexing (for example, removal of characters,
 and base-letter conversion) are also performed on query terms at query time. This ensures
 that the query terms match the form of the tokens in the Text index.

BASIC_LEXER supports any database character set.

BASIC_LEXER attribute printjoins:
    Specify the non alphanumeric characters that, when they appear anywhere in a word (beginning, middle, or end), are processed as alphanumeric and included with the token in the Text index. This includes printjoins that occur consecutively.

    For example, if the hyphen '-' and underscore '_' characters are defined as printjoins, terms such as pseudo-intellectual and _file_ are stored in the Text index as pseudo-intellectual and _file_.
    
BASIC_LEXER ExampleThe following example sets printjoin characters and disables theme indexing with the BASIC_LEXER:

begin
ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES');
end;

To create the index with no theme indexing and with printjoins characters set as described, issue the following statement:

create index myindex on mytable ( docs ) indextype is ctxsys.context parameters ( 'LEXER mylex' );

domain index is marked LOADING/FAILED/UNUSABLE

domain index is marked LOADING/FAILED/UNUSABLE

Replicate the error
CREATE TABLE my_docs (
   id    NUMBER(10)     NOT NULL,
   name  VARCHAR2(200)  NOT NULL,
   doc   BLOB           NOT NULL
 );


 ALTER TABLE my_docs ADD (
   CONSTRAINT my_docs_pk PRIMARY KEY (id)
 );


 CREATE SEQUENCE my_docs_seq;


 CREATE OR REPLACE DIRECTORY documents AS 'C:\work';


 CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name  IN  my_docs.name%TYPE) AS
   v_bfile      BFILE;
   v_blob       BLOB;
 BEGIN
   INSERT INTO my_docs (id, name, doc)
   VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
   RETURN doc INTO v_blob;

   v_bfile := BFILENAME('DOCUMENTS', p_file_name);
   Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
   Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
   Dbms_Lob.Fileclose(v_bfile);

   COMMIT;
 END;
 /



create index idx_domain on my_docs(doc) indextype is ctxsys.context parameters ('sync (on commit)');

Now Rebuild the index "idx_domain" to replicate the error , on same time i execute below command and it throws error:

SQL>  EXEC load_file_to_my_docs('try.pdf');
BEGIN load_file_to_my_docs('try.pdf'); END;

*
ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
ORA-06512: at "G.LOAD_FILE_TO_MY_DOCS", line 5
ORA-06512: at line 1

Solution::
Rebuilds Domain index/ full text index as below:::
 
ALTER INDEX your_index REBUILD ONLINE PARAMETERS ('REPLACE LEXER your_lexer');