http://bytes.com/topic/sql-server/answers/640806-create-table-copy-existing-table
Question:
May 2nd, 2007, 12:57 PM
Shrutisinha (Newbie)
Join Date: Feb 2007 Posts: 25
Hi Guys
Really need your help I donno what I am doing wrong in here
Want to create a table with another existing table
Here is the syntax I am using
create table pctemp1
As
(SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date]
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null )
and it says Syntax error with AS clause, tried removing AS clause but no go , can anybody help me please ...
thanks
Answer:
May 2nd, 2007, 03:51 PM
iburyak (Expert)
Join Date: Nov 2006 Posts: 1,017
re: Create table as copy of existing table
Try this:
SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date] into pctemp1
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null
Good Luck.
Monday, August 31, 2009
MCTS 70-433 SELECT .. INTO
MCTS 70-433 @@TRANCOUNT
I'm not sure if @@trancount is on the 70-433 exam but I thought the following was interesting:
http://codebetter.com/blogs/darrell.norton/archive/2003/12/24/4921.aspx
SQL Server Transactions - Commit and Rollback
A coworker was having trouble with a stored proc. There were several IF statements where one path created another nested level transaction while the other did not. Trying to get the stored proc to COMMIT only certain transactions by name was sometimes generating an error that there was no corresponding BEGIN TRANSACTION. What we learned was the following.
Whenever you execute a COMMIT TRANSACTION statement, any transaction name after the statement is ignored. The only thing a COMMIT TRANSACTION statement does is reduce the @@trancount variable by 1. If this makes @@trancount = 0, then all database modifications are committed.
For example, say you have the following code (from SQL Server Books Online):
CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
BEGIN TRANSACTION OuterTran -- @@TRANCOUNT set to 1.
GO
INSERT INTO TestTran VALUES (1, 'aaa')
GO
BEGIN TRANSACTION Inner1 -- @@TRANCOUNT set to 2.
GO
INSERT INTO TestTran VALUES (2, 'bbb')
GO
BEGIN TRANSACTION Inner2 -- @@TRANCOUNT set to 3.
GO
INSERT INTO TestTran VALUES (3, 'ccc')
GO
COMMIT TRANSACTION Inner2 -- Decrements @@TRANCOUNT to 2.
-- Nothing committed.
-- ROLLBACK TRANSACTION Inner1
GO
COMMIT TRANSACTION Inner1 -- Decrements @@TRANCOUNT to 1.
-- Nothing committed.
GO
COMMIT TRANSACTION OuterTran -- Decrements @@TRANCOUNT to 0.
-- Commits outer transaction OuterTran.
GO
DROP TABLE TestTran
MCTS 70-433 calling procedures with output keyword
The exam requires you are aware of the OUTPUT keyword, typically this is used to pass the value of an external stored procedure to an external variable.
The following gives a simple example procedure and some discussions about how to integrate with .NET:
In .net you both Parameter and SQLParameter types have properties for direction. Each specify 4 types of directions, Input, Output, InputOutput and Return. I'm writing a wrapper web service for executing stored procedures on our database and I have a method to that retrieves all parameters for a stored proc as well as all the info for each param. The problem I am having is I can't figure out how do I get an OUTPUT param. How do you write a stored procedure in which there is an output parameter? Any time I declare a passed parameter in my stored procedure as OUTPUT .net looks at it as InputOutput. In the simple example below .net looks at both params as InputOutput....
CREATE PROCEDURE [dbo].[SLT_NFLDivisionOUTDemo]
@AFC AS INT = 0 OUTPUT,
@NFC AS INT = 0 OUTPUT
AS
BEGIN
SET @AFC = (SELECT COUNT(*) FROM NFL_DEMO WHERE Division = 'AFC')
SET @NFC = (SELECT COUNT(*) FROM NFL_DEMO WHERE Division = 'NFC')
RETURN
END
1st Answer:
Could you post your .NEt code here. you can do something like this,
SqlParameter para = new SqlParameter("@clientId", client.ClientId);
para.Direction = ParameterDirection.Output;
command.Parameters.Add(para); // SqlCommand
Hasmukh
2nd Answer:
For SQL Server, stored procedure parameters that are declared with the OUTPUT keyword are actually INOUT. The only parameters you will find in INFORMATION_SCHEMA.PARAMETERS that have PARAMETER_MODE = 'OUT' are name-less parameters that represent the return value of scalar-valued user defined functions.
Also, moving this thread to SQL Server Data Access Forum, where it will have more chances than in the Entity Framework forums.
Thanks,
Diego MSFT
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/bc44e0a5-5dd8-45cb-9c05-76076d5f56f6
MCTS 70-433 calling functions with default keyword
When a parameter of the function has a default value, the keyword "default" must be specified when calling the function in order to get the default value.
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )RETURNS scalar_return_data_type
[ WITH <> [ [,] ...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
@parameter_name
Is a parameter in the user-defined function. One or more parameters can be declared in a CREATE FUNCTION statement. A function can have a maximum of 1,024 parameters. The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined. When a parameter of the function has a default value, the keyword "default" must be specified when calling the function in order to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value.
http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx
MCTS 70-433 sp_addmessage
Question: How can I add a user-defined error message?
Answer: You can use the sp_addmessage system stored procedure to add a user-defined error message.
You can specify the id of the message (value greater than 50000), severity level from 0 through 25 (to add a message with a severity level from 19 through 25, you must be a member of the sysadmin fixe server role) and the message text (up to 255 characters).
By the way, only members of the sysadmin and serveradmin fixed server roles can execute the sp_addmessage system stored procedure.
This is the example:
USE master
EXEC sp_addmessage @msgnum = 50001,
@severity = 18,
@msgtext = 'Custom error message'
See SQL Server Books Online for more details about the sp_addmessage system stored procedure.
http://www.mssqlcity.com/FAQ/Admin/add_user_defined_error_message.htm
MCTS 70-433 XACT_ABORT
SET XACT_ABORT { ON | OFF }
Remarks
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
http://msdn.microsoft.com/en-us/library/ms188792.aspx
MCTS 70-433 Enable "clr Enabled" configuration option
Problem - How to enable "clr Enabled" configuration option?
I've Created a DLL Library and added it as an assembly in a database when I make a select statement from SQLCmd using my functions it runs fine but when I try to Create View from the VB 2005 Express Development Environment it Gives me the Error
SQL Execution Error
Error Message: Execution of user code in the .net Framework is disabled. Enable "clr Enabled" configurartion option
note that CLR Integration Option of the SQL server is ON and I've Tried
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE
form SQLCMD in vain
when I execute the select statement from the SQLcmd it runs fine but when I try to make a View from the Developmet Environment i gives me that error
Solution
I changed my query to
Use [DatabaseName]
Go
Exec SP_Configure 'CLR_Enabled', '1'
Go
Reconfigure
Go
Alter Database [DatabaseName] Set Trustworthy On
Go
and got it to work...
http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/7ad768fa-5e23-4915-839f-570c0b4a8eb3
MCTS 70-433 Nested Transactions in SQL Server
Nested transaction are always fun, I remember once chasing own a transaction (J2EE Weblogic / Oracle) which the developer saw as committed in their code but was somehow getting roled backed ...
http://stackoverflow.com/questions/851441/nested-transactions-in-sql-server
Question: Does sql server allow nested transactions? If so then whats the priority of transactions?
Most Popular Answer: From the MSDN documentation on SQL Server. Nesting Transactions:
Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
MCTS 70-433 Security for CLR External Assembly
You can create a CLR External Assembly (a DLL of external code) but you will need to have appropriate permissions set:
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/b27e8e16-2d84-4eec-a87d-df53b2e298b
- CREATE ASSEMBLY failed: not authorized for PERMISSION_SET = EXTERNAL_ACCESS
- Wednesday, April 19, 2006 8:27 PM
- AndrewAPlus
CREATE ASSEMBLY ClrSql from 'C:\ClrSql.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
gives me this . . .
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'ClrSql' failed because assembly 'ClrSql' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
How do I allow EXTERNAL_ACCESS and UNSAFE permissions? The property appears grayed out, and I am the dbo.
ReplyQuote
- Wednesday, April 19, 2006 10:05 PM
Jens K. Suessmeyer -MSFT, Moderator
Hi,
the quickfix would be to set the database to trustworthy:
ALTER DATABASE SomeDatabase SET TRUSTWORTHY ON
Another option would be to make the assembly conformable to the requirements as mentioned in the error message.
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
MCTS 70-433 implicit transaction and default behaviour
Coming from an oracle background, I'm used to having explicitly commit my transactions as the default behaviour in the database, in SQL Server this is not always the default:
After implicit transaction mode has been set on for a connection, SQL Server automatically starts a transaction when it first executes any of these statements:
ALTER TABLE
INSERT
CREATE
OPEN
DELETE
REVOKE
DROP
SELECT
FETCH
TRUNCATE TABLE
GRANT
UPDATE
The transaction remains in effect until you issue a COMMIT or ROLLBACK statement...
Implicit transaction mode is set either using the Transact-SQL SET statement, or through database API functions and methods.
http://msdn.microsoft.com/en-us/library/aa213064(SQL.80).aspx
The API mechanisms used to set implicit transactions are ODBC and OLE DB.
ODBC
- Call the SQLSetConnectAttr function with Attribute set to SQL_ATTR_AUTOCOMMIT and ValuePtr set to SQL_AUTOCOMMIT_OFF to start implicit transaction mode.
- The connection remains in implicit transaction mode until you call SQLSetConnectAttr with Attribute set to SQL_ATTR_AUTOCOMMIT and ValuePtr set to SQL_AUTOCOMMIT_ON.
- Call the SQLEndTran function with CompletionType set to either SQL_COMMIT or SQL_ROLLBACK to commit or roll back each transaction.
- When SQL_AUTOCOMMIT_OFF is set by an ODBC application, the Microsoft® SQL Server™ ODBC driver issues a SET IMPLICIT_TRANSACTION ON statement
MCTS 70-433 CLR Trigger
This is a bit advanced for the 70-433 exam but I've included it as interesting background.
The following bulletin board thread gives good example of basic CLR triggers. It illustrates why you might need to use them, how to set one up and just how tricky this can be:
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/f3b0c009-7669-4090-9867-4adf15f1e66e
CLR Trigger
- Wednesday, July 13, 2005 9:30 AM
Vinoth
Hi,- I want to call webservice in the Trigger. How to call the webservice from CLR Triggers?.If anybody knows teh detail let me know.
- And i worked out the following Example code for CLR Trigger.
- using System.Data;
- using System.Collections.Generic;
- using System.Text;
- using System.Data.Sql;
- using System.Data.SqlTypes;
- using System.Data.SqlServer;
- namespace CLR
- {
- public class Class1
- {
- public static void InsertTrigger()
- {
- SqlTriggerContext sqlTrigger = SqlContext.GetTriggerContext();
- SqlPipe sqlPipe = SqlContext.GetPipe();
- SqlCommand cmd = SqlContext.GetCommand();
- if (sqlTrigger.TriggerAction == TriggerAction.Insert)
- {
- cmd.CommandText = "Select * from Inserted";
- SqlDataRecord dr = cmd.ExecuteRow();
- string Subject_uri = dr[0].ToString();
- string predicate = dr[1].ToString();
- string Obj = dr[2].ToString();
- sqlPipe.Execute(cmd);
- sqlPipe.Send("You Selected Subject_uri ::"+Subject_uri+" Predicate ::"+predicate+" Object ::"+Obj);
- }
- }
- }
- }
- After that i created Assembly and Trigger and clr enabled
- create assembly CLR from 'C:\Inetpub\wwwroot\Vinoth\CLR\bin\Debug\CLR.dll'
- CREATE TRIGGER InsertTrigger
- ON Triplets
- For Insert
- As
- External Name
- CLR.[CLR.Class1].InsertTrigger
- sp_configure 'show advanced options', 1
- RECONFIGURE
- sp_configure 'clr enabled', 1
- RECONFIGURE
- insert into Triplets values('test','_2','Testing1')
- When i insert the values it is inserting into table .But why ican't get sqlpipe.send Message.
- How do i know whether my CLR trigger is working?
- Thanks,
- Vinoth
Answers
- Monday, August 01, 2005 1:55 AM
Pablo Castro - MSFT
To answer the specific question: pipe.Send(string) is the equivalent of "PRINT" in T-SQL. In order to "see" the string you sent from the server in the client you need to be listening to info-messages.
If you're using System.Data.SqlClient in the client, the way to do this is to subscribe to the InfoMessage event in the SqlConnection object. If you're trying this from Query Analyzer or Management Studio, then you'll see it in the "messages" tab after executing the query.
I have to extra comments:
1. Note that when you move to newer CTPs or Beta versions (or the RTM version) you'll have to update your code as we no longer have a separate System.Data.SqlServer namespace for inproc data-access. That capability has been integrated into the provider in System.Data.SqlClient.
2. Doing network I/O -particularly over the internet- inside a trigger can seriously impact the performance of your database. I can't say "don't do it" because I don't have understanding of your particular scenario, so let me say this: I recommend that you do this only if you really need to and understand the implications of doing so.
Hope this helps.
MCTS 70-433 DDL Triggers
There limitation to using DDL Triggers to rollbck transactions:
Steps to Reproduce
1. Execute the following code:
CREATE TRIGGER preventalter
ON ALL SERVER
FOR ALTER_DATABASE
AS
PRINT 'You are in production. Alter database not allowed.'
ROLLBACK TRANSACTION
GO
2. Now change the recovery model
Actual Results
DDL trigger fires as evidenced by the error message being displayed. However, the recovery model is still changed.
Expected Results
DDL trigger fires, displays error message, and prevents the recovery model from being changed.
The problem is:
Most operations for ALTER DATABASE are not transacted operations, and so they do not adhere to transactions. ALTER DATABASE also cannot be run in a user transaction. The fact that the recovery mode change happened in this case is expected behavior.
Posted by Microsoft on 8/31/2006 at 3:34 PM
However for other transactional operations like creating stored procedures, DDL trigger to allow you to build in validation routines.
MCTS 70-433 Table-Valued Parameters
The use of Table-Valued Parameters is slightly advanced, but they are strongly typed (safer / better design) and can be a useful tool for advanced programmers:
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO
Restrictions
Table-valued parameters have the following restrictions:
- SQL Server does not maintain statistics on columns of table-valued parameters.
- Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
- You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits:Benefits
http://msdn.microsoft.com/en-us/library/bb510489.aspx
MCTS 70-433 DDL event triggers - "AFTER DROP TABLE"
The "AFTER DROP TABLE" is just one of many DDL events you can associate a trigger with:
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH
[ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <> [ ; ] }
::= [ ENCRYPTION ]
[ EXECUTE AS Clause ]
http://msdn.microsoft.com/en-us/library/ms189799.aspx
There are lots of DDL events, here are a few:
CREATE_SPATIAL_INDEX
ALTER_INDEX can be used for spatial indexes.
DROP_INDEX can be used for spatial indexes.
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYMMETRIC_KEY
ALTER_SYMMETRIC_KEY
DROP_SYMMETRIC_KEY
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE (Applies to the ALTER TABLE statement and sp_tableoption.)
DROP_TABLE
CREATE_TRIGGER
ALTER_TRIGGER (Applies to the ALTER TRIGGER statement and sp_settriggerorder.)
DROP_TRIGGER
http://msdn.microsoft.com/en-us/library/bb522542.aspx
MCTS 70-433 CREATE FUNCTION SIGNATURE
This example show a simple working function call:
create function dbo.fn_call_stp ()
returns int
as
begin
return (
SELECT top (1) c
FROM OPENROWSET('SQLNCLI', 'Server=servername;Trusted_Connection=yes;',
'execute dbname.dbo.stp_call_from_udf') AS a)
end
There is a gotcha with function calls, if the return type is int(10) then returning a vanila int?
MCTS 70-433 RAISERROR usage
This example gives a good overview of basic usage:
Without seeing your code, I can't tell what is wrong. But it sounds like
you should have an error handler like:
BEGIN CATCH
IF error_number() <> 2627
BEGIN
SELECT @msg = error_message(),
@sev = error_severity(),
@state = error_state()
RAISERROR (@msg, @sev, @state)
END
END CATCH
Then again, I would sugest that it is better to add an IF NOT EXISTS
to avoid the PK violation altogether.
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/74698/Sql-server-2005-2008-try-catch-raiserror
For the technical details from msdn:
RAISERROR (Transact-SQL)
Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.
Transact-SQL Syntax Conventions
Syntax
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
RAISERROR (N'This is message %s %d.', -- Message text.
10, -- Severity,
1, -- State,
N'number', -- First argument.
5); -- Second argument.
-- The message text returned is: This is message number 5.
GO
MCTS 70-433 Storing Binary Objects / Files in SQL Server
To store FILESTREAM data (image or text files) you need to use the varbinary(max) datatype:
Create table photos(
photoID int NOT NULL IDENTITY(1,1),
photo varbinary(max) FILESTREAM NULL);
MCTS 70-433 Non-clustered Index Basics
The table has a clustered index on empID.
The HR is searching for department and hire year ... create a non-clustered index on publishers and release year.
MCTS 70-433 Partition Functions and Partition Schemes
Step one - define you partition function
create partition function partition_function (int) as range right for values (10000,20000,30000);
Step two - build your partition scheme based on your partition function
create partition scheme partition_scheme
as partition partition_function to (part1, part2, part3, part4);
Step three - build table based on partition scheme
create table employee (
empID int,
empName varchar(50)) on partition_scheme (empID)
MCTS 70-433 SQL Server (and DB2) do not allow more than one NULL value in a unique index column!
As primarily an Oracle DBA (Oracle allowing multiple NULL values within a unique index column):
Back to the Basics: Difference between Primary Key and Unique Index
Posted by decipherinfosys on July 4, 2007
Here is another post in the back to the basics section: What is the difference between a Primary Key and a Unique Index? Both can be declared on one or more columns, both can be used to enforce foreign keys (if the unique index is on not null column(s)), both can be declared as clustered/non clustered indexes (SQL Server lingo), both can be used on computed columns as well (SQL Server).
The differences between the two are:
- Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
- There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
- Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. This is just the default behavior though and can be changed at creation time, if needed.
http://decipherinfosys.wordpress.com/2007/07/04/back-to-the-basics-difference-between-primary-key-and-unique-index/
MCTS 70-433 SQL Server Violating Constraints
In exception circumstances you may want to temporarily disable a constraint:
alter table employee NOCHECK CONSTRAINT Age_Check; (disable)
alter table employee CHECK CONSTRAINT Age_Check; (re-enable)
MCTS 70-433 SQL Server partition_function "range right"
A simple example ...
create partition function partition_function (int) as range right for values (10000,20000,30000);
create partition scheme partition_scheme
as partition partition_function to (part1, part2, part3, part4);
create table employee (
empID int,
empName varchar(50)) on partition_scheme (empID)
Then employee 20000 ends up in part3 ...
MCTS 70-433 SQL Server WITH CHECK OPTION of VIEWs
Suppose standard HR staff have been restricting to viewing staff under grade 6 ...
create view grunts as
select empid, empname, ... from employees where grade <>
Here using the "WITH CHECK OPTION" prevents the grunts view being used in an update statement which violates the grade <>
MCTS 70-433 SQL Server creating CLR stored procedures written using the .NET framework
To create a CLR stored procedure you must include the "EXTERNAL NAME" clause of the CREATE PROCEDURE statement.
You are in effect defining a .NET static class method.
After compile the class, you must then register the assembly in SQL Server using CREATE ASSEMBLY.
The following article gives a good over of how to work CLR stored procedures, I have cut-out a key piece here, the full article is more detailed:
By default the SQL Server .NET Data Provider is added as a reference, along with an include statement for its System.Data.SqlServer namespace. Plus, you can see the System.Data reference, which provides support for ADO.NET and its data-oriented objects such as the DataSet and the System.Data.SqlTypes namespace that provides support for the SQL Server data types.
It's up to you to fill in the rest of the code that makes the stored procedure work. The following example illustrates the source code required to create a simple CLR stored procedure that imports the contents of a file into a varchar or text column:
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class StoredProcedures
_
Public Shared Sub usp_ImportFile _
(ByVal sInputFile As String, ByRef sColumn As String)
Dim sContents As String
Try
Dim stmReader As New StreamReader(sInputFile)
sContents = stmReader.ReadToEnd()
stmReader.Close()
sColumn = sContents
Catch ex As Exception
Dim sp As SqlPipe = SqlContext.Pipe()
sp.Send(ex.Message)
End Try
End Sub
End Class
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1251402,00.html
MCTS 70-433 SQL Server Concurrency and Phantom Reads
To prevent dirty / phantom / non-repeatedable reads, you need an "isolation level" with high degree of concurrency
.
In SQL2005 there are five types of isolation, show here in order of increasing degree of concurrency:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SNAPSHOT
- SERIALIZABLE
The difference between SNAPSHOT and SERIALIZABLE is quite complex (SNAPSHOT working via a table lock and row versiosns and SERIALIZABLE working via page and row lock), but both prevent phantom reads.
SERIALIZABLE
This isolation level specifies that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other. The DBMS may execute two or more transactions at the same time only if the illusion of serial execution can be maintained. At this isolation level, phantom reads cannot occur.
However, many databases (e.g. Oracle[1], PostgreSQL[2]) do not guarantee that there is some serial ordering of the transactions that will result in the same outcome, instead implementing snapshot isolation. This explicitly contradicts the ANSI/ISO SQL 99 standard (see ISO/IEC9075-2:1999(E), page 83). Other databases (MS SQL Server[3]) support both serializable and snapshot isolation modes.
With a lock-based concurrency control DBMS implementation, serializability requires that range locks are acquired when a query uses a ranged WHERE clause. When using non-lock concurrency control, no lock is acquired; however, if the system detects a concurrent transaction in progress which would violate the serializability illusion, it must force that transaction to roll back, and the application will have to restart the transaction.
http://en.wikipedia.org/wiki/Isolation_(database_systems)