You are currently browsing the tag archive for the 'SQLServer' tag.
http://msdn.microsoft.com/en-us/library/ms186778.aspx
SQL Server supports a three-part naming convention when you refer to the current server. The ISO standard also supports a three-part naming convention. However, the names used in both naming conventions are different. The information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. The following table shows the relationships between the SQL Server names and the SQL standard names.
| SQL Server name | Maps to this equivalent SQL standard name |
| Database | Catalog |
| Schema | Schema |
| Object | Object |
| user-defined data type | Domain |
This name-mapping convention applies to the following SQL Server ISO-compatible views.
Also, some views contain references to different classes of data such as character data or binary data.
When you reference the information schema views, you must use a qualified name that includes the INFORMATION_SCHEMA schema name. For example:
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N’Product’;
GO
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t
ON c.user_type_id=t.user_type_id
where OBJECT_NAME(c.OBJECT_ID)=’salary_table_detail’
ORDER BY c.OBJECT_ID;
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t
ON c.user_type_id=t.user_type_id
where OBJECT_NAME(c.OBJECT_ID)=’salary_table_detail’
ORDER BY c.OBJECT_ID;
http://www.sqlteam.com/article/introduction-to-transactions
This article covers the basic of transactions. It describes them and gives examples of committing and rolling back transactions. It also shows how to properly trap errors inside a stored procedure using transactions.
<!–
–>
Books Online defines a transaction as a “sequence of operations performed as a single logical unit of work”. A transaction has four key properties that are abbreviated ACID. ACID is an acronym for for Atomic Consistent Isolated Durability.Atomic means that all the work in the transaction is treated as a single unit. Either it is all performed or none of it is. Consistent means that a completed transaction leaves the database in a consistent internal state. Isolations means that the transaction sees the database in a consistent state. This transaction operates on a consistent view of the data. If two transactions try to update the same table, one will go first and then the other will follow. Durability means that the results of the transaction are permanently stored in the system.
The simplest transaction in SQL Server is a single data modification statement. All my examples use the pubs database. The following
UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176'
is a transaction even though it doesn’t do much. It’s called an Autocommit transaction. SQL Server first writes to the log file what it’s going to do. Then it does the actual update statement and finally it writes to the log that it completed the update statement. The writes to the log file are written directly to disk but the update itself is probably done to a copy of the data that resides in memory. At some future point that database will be written to disk. If the server fails after a transaction has been committed and written to the log, SQL Server will use the transaction log to “roll forward” that transaction when it starts up next.
Multi-Statement Transactions
To make transactions a little more usefull you really need to put two or more statements in them. These are called Explicit Transactions. For example,
BEGIN TRAN UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176' UPDATE authors SET au_fname = 'Marg' WHERE au_id = '213-46-8915' COMMIT TRAN
Note that we have a BEGIN TRAN at the beginning and a COMMIT TRAN at the end. These statements start and complete a transaction. Everything inside these statements is considered a logical unit of work. If the system (Note: change statement to system for clarity) fails after the first update, neither update statement will be applied when SQL Server is restarted. The log file will contain a BEGIN TRAN but no corresponding COMMIT TRAN.
Rolling Back
You can also roll back a transaction if it doesn’t do what you want. Consider the following transaction:
BEGIN TRAN UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176' UPDATE authors SET au_fname = 'JohnY' WHERE city = 'Lawrence' IF @@ROWCOUNT = 5 COMMIT TRAN ELSE ROLLBACK TRAN
Suppose that for whatever reason, the second update statement should update exactly five rows. If @@ROWCOUNT, which hold the number of rows affected by each statement, is five then the transaction commits otherwise it rolls back. The ROLLBACK TRAN statement “undoes” all the work since the matching BEGIN TRAN statement. It will not perform either update statement. Note that Query Analyzer will show you messages indicating that rows were updated but you can query the database to verify that no actual data modifications took place.
Stored Procedures
Hopefully most of your transactions will occur in stored procedures. Let’s look at the second example inside a stored procedure.
Create Proc TranTest1
AS
BEGIN TRAN
INSERT INTO [authors]([au_id],
[au_lname],
[au_fname],
[phone],
[contract])
VALUES ('172-32-1176',
'Gates',
'Bill',
'800-BUY-MSFT',
1)
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'
COMMIT TRAN
GO
The problem with this stored procedure is that transactions don’t care if the statements run correctly or not. They only care if SQL Server failed in the middle. If you run this stored procedure, it will try to insert a duplicate entry into the authors database. You’ll get a primary key violation error message. The message will even tell you the statment has been terminated. But the transaction is still going. The UPDATE statement runs just fine and SQL Server then commits the transaction. The proper way to code this is:
Create Proc TranTest2
AS
BEGIN TRAN
INSERT INTO [authors]([au_id],
[au_lname],
[au_fname],
[phone],
[contract])
VALUES ('172-32-1176',
'Gates',
'Bill',
'800-BUY-MSFT',
1)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 11
END
COMMIT TRAN
GO
You’ll notice that we check each statement for failure. If the statement failed (i.e. @@ERROR <> 0) then we rollback the work performed so far and use the RETURN statement to exit the stored procedure. It’s very important to note that if we don’t check for errors after each statement we may commit a transaction improperly.
http://www.issociate.de/board/post/244540/Get_data_type_of_column.html
sp_columns <Table_name>
select table_name as Name from
INFORMATION_SCHEMA.Tables
where TABLE_TYPE = ‘BASE TABLE’
order by Name
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Func_GetRangeDate]
(
@start datetime,
@end datetime
)
RETURNS
@RangeDate TABLE
(
date datetime
)
AS
BEGIN
Declare @day int
Declare @index int
Set @day = datediff(day,@start,@end)
Set @index = 0
while @index < @day
begin
Insert into @RangeDate select dateadd(day,@index,@start)
Set @index = @index + 1
end
RETURN
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Name
– Create date:
– Description:
– =============================================
ALTER FUNCTION [dbo].[getSalaryFunc]
(
– Add the parameters for the function here
@date datetime
)
RETURNS
@access TABLE
(
– Add the column definitions for the TABLE variable here
card_id varchar(31),
date datetime,
salary numeric(2,1)
)
AS
BEGIN
– Fill the table variable with the rows for your result set
insert into @access
select card_id, date, salary
from Access
where date=@date
order by card_id, date
RETURN
END
http://wardyit.com/blog/blog/archive/2005/11/02/51.aspx
Date for Monday and Friday of Current Week
The Date for Monday and Friday of the current week can be determined as illustrated below.
SELECT GETDATE() + (2 – DATEPART(WEEKDAY, GETDATE())) AS MONDAY, GETDATE() + (6 – DATEPART(WEEKDAY, GETDATE())) AS FRIDAY
This assumes that you consider the first day of the week to be a Sunday and that the last day of the week is a Saturday. If this is not the case you will need to use the SET DATEFIRST to set the first day of the week to a number from 1 through 7. ie. SET DATEFIRST 1 to set MONDAY. To determine the day that is currently set as the first day of the week run SELECT @@DATEFIRST (by default this is 7 which is Sunday).
