These are the top SQL Searches on this site for the month of August I have left out searches that have nothing to do with SQL Server or programming (for example atlantic city escorts)
Here are the results...
dtsrun from sp
query multiple databases
first business day of each month query
Truncated table recovery
check constraint
dbcc report files
first business day of each month
String or binary data would be truncated.
SQL SELECT *
substr()
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
xp_fileexist
SQL SELECT WHERE DATE
CONCAT_NULL_YIELDS_NULL
check constrant
SQL 2000 parallel backup restore
dtsrun
Let's talk about a couple of these
query multiple databases
I covered that in this post
first business day of each month query
You really need to have a calendar table for this one. You can also use a number table and check for the min date where select datepart(dw,date) between 2 and 6 but what about holidays. A calendar table is your best bet. And I know just a place to get some code for that-->A way to load a calendar table
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
That can be found here: 2000 version, 2005 version
xp_fileexist
That is covered here
String or binary data would be truncated
And that was covered here
So there you have it, those were some of the searches and I covered some of that stuff already. I always like to look at the searches because it gives me ideas for future blog posts
Friday, September 01, 2006
Top 5 Posts For The Month Of August
Below are the top 5 posts according to Google Analytics for the month of August
Here are the posts in order by pageviews descending
Store The Output Of A Stored Procedure In A Table Without Creating A Table
6 Different Ways To Get The Current Identity Value From A Table
COALESCE And ISNULL Differences
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
And I have also updated the Top 10 Articles of all time
Here are the posts in order by pageviews descending
Store The Output Of A Stored Procedure In A Table Without Creating A Table
6 Different Ways To Get The Current Identity Value From A Table
COALESCE And ISNULL Differences
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
And I have also updated the Top 10 Articles of all time
Thursday, August 31, 2006
sys.dm_exec_sessions
As I promised earlier today, here is the first of many posts about the Dynamic Management Views in SQL Server 2005.
What does the sys.dm_exec_sessions view return? It looks like it is a combination of DBCC USEROPTION and sp_who. Let's for example find out how many sessions are running, sleeping or dormant
Just a quick count of all the sessions
SELECT COUNT(*) as StatusCount,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
GROUP BY status
Let's list all SPID's
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
Let's just grab our own SPID
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
Now let's query all user sessions
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =0
Then for the server you would change 0 to 1
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1
Just a quick count of all the transaction isolation levels
SELECT COUNT(*),CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
GROUP BY transaction_isolation_level
Let's look at some of these ANSI settings and while we are at it let's use UNPIVOT to return the results vertically
SELECT SPID,Value,ANSI_SETTING
FROM (
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID ) P
UNPIVOT (Value for ANSI_SETTING IN(
QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
ANSI_DEFAULTS,ANSI_WARNINGS,
ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
)
) AS unpvt
Now let's see if we can duplicate DBCC USEROPTIONS
First run DBCC USEROPTIONS
And then run the following:
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL,lock_timeout,date_first,date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
It's probably not exactly the same and I don't know if I missed a column but below is the whole definition of the view and you can experiment with this yourself
Below is what the sys.dm_exec_sessions view returns
On the first line are the column name and the data type, below that is the description
sys.dm_exec_sessions
session_id smallint
Identifies the session associated with each active primary connection.
login_time datetime
Time when session was established.
host_name nvarchar(128)
Host associated with the session.
program_name nvarchar(128)
Program associated with the session.
host_process_id int
Process ID associated with the session.
client_version int
Version of the interface used by the client to connect to the Server.
client_interface_name nvarchar(32)
Name of the interface used by the client to connect to the Server.
security_id varbinary(85)
Microsoft Windows security ID associated with the login.
login_name nvarchar(128)
SQL Login Name associated with the session.
nt_domain nvarchar(128)
Domain from which session connection was made.
nt_user_name nvarchar(128)
Name of the user associated with the session.
status nvarchar(30)
Status of the session. Possible values:
-- Running - Currently running one or more requests
-- Sleeping - Currently running no requests
-- Dormant – Session is in prelogin state
context_info varbinary(128)
CONTEXT_INFO value for the session.
cpu_time int
CPU time, in milliseconds, that was used by this session.
memory_usage int
Number of 8-KB pages of memory used by this session.
total_scheduled_time int
Total time, in milliseconds, for which the session (requests within) were scheduled for execution.
total_elapsed_time int
Time, in milliseconds, since the session was established.
endpoint_id int
ID of the Endpoint associated with the session.
last_request_start_time datetime
Time at which the last request on the session began. This includes the currently executing request.
last_request_end_time datetime
Time of the last completion of a request on the session.
reads bigint
Number of reads performed, by requests in this session, during this session.
writes bigint
Number of writes performed, by requests in this session, during this session.
logical_reads bigint
Number of logical reads that have been performed on the session.
is_user_process bit
0 if the session is a system session. Otherwise, it is 1.
text_size int
TEXTSIZE setting for the session.
language nvarchar(128)
LANGUAGE setting for the session.
date_format nvarchar(3)
DATEFORMAT setting for the session.
date_first smallint
DATEFIRST setting for the session.
quoted_identifier bit
QUOTED_IDENTIFIER setting for the session.
arithabort bit
ARITHABORT setting for the session.
ansi_null_dflt_on bit
ANSI_NULL_DFLT_ON setting for the session.
ansi_defaults bit
ANSI_DEFAULTS setting for the session.
ansi_warnings bit
ANSI_WARNINGS setting for the session.
ansi_padding bit
ANSI_PADDING setting for the session.
ansi_nulls bit
ANSI_NULLS setting for the session.
concat_null_yields_null bit
CONCAT_NULL_YIELDS_NULL setting for the session.
transaction_isolation_level smallint
Transaction isolation level of the session.
-- 0 = Unspecified
-- 1 = ReadUncomitted
-- 2 = ReadCommitted
-- 3 = Repeatable
-- 4 = Serializable
-- 5 = Snapshot
lock_timeout int
LOCK_TIMEOUT setting for the session. The value is in milliseconds.
deadlock_priority int
DEADLOCK_PRIORITY setting for the session.
row_count bigint
number of rows returned on the session up to this point.
prev_error int
ID of the last error returned on the session.
So that's it for today, the view contains a lot more than I covered. i might expand this if I have time. If you have any suggestions leave me a comment or drop me an Email
The link to the post with all the views is here (as of today this is the only view that is covered)
What does the sys.dm_exec_sessions view return? It looks like it is a combination of DBCC USEROPTION and sp_who. Let's for example find out how many sessions are running, sleeping or dormant
Just a quick count of all the sessions
SELECT COUNT(*) as StatusCount,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
GROUP BY status
Let's list all SPID's
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
Let's just grab our own SPID
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
Now let's query all user sessions
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =0
Then for the server you would change 0 to 1
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1
Just a quick count of all the transaction isolation levels
SELECT COUNT(*),CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
GROUP BY transaction_isolation_level
Let's look at some of these ANSI settings and while we are at it let's use UNPIVOT to return the results vertically
SELECT SPID,Value,ANSI_SETTING
FROM (
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID ) P
UNPIVOT (Value for ANSI_SETTING IN(
QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
ANSI_DEFAULTS,ANSI_WARNINGS,
ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
)
) AS unpvt
Now let's see if we can duplicate DBCC USEROPTIONS
First run DBCC USEROPTIONS
And then run the following:
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL,lock_timeout,date_first,date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
It's probably not exactly the same and I don't know if I missed a column but below is the whole definition of the view and you can experiment with this yourself
Below is what the sys.dm_exec_sessions view returns
On the first line are the column name and the data type, below that is the description
sys.dm_exec_sessions
session_id smallint
Identifies the session associated with each active primary connection.
login_time datetime
Time when session was established.
host_name nvarchar(128)
Host associated with the session.
program_name nvarchar(128)
Program associated with the session.
host_process_id int
Process ID associated with the session.
client_version int
Version of the interface used by the client to connect to the Server.
client_interface_name nvarchar(32)
Name of the interface used by the client to connect to the Server.
security_id varbinary(85)
Microsoft Windows security ID associated with the login.
login_name nvarchar(128)
SQL Login Name associated with the session.
nt_domain nvarchar(128)
Domain from which session connection was made.
nt_user_name nvarchar(128)
Name of the user associated with the session.
status nvarchar(30)
Status of the session. Possible values:
-- Running - Currently running one or more requests
-- Sleeping - Currently running no requests
-- Dormant – Session is in prelogin state
context_info varbinary(128)
CONTEXT_INFO value for the session.
cpu_time int
CPU time, in milliseconds, that was used by this session.
memory_usage int
Number of 8-KB pages of memory used by this session.
total_scheduled_time int
Total time, in milliseconds, for which the session (requests within) were scheduled for execution.
total_elapsed_time int
Time, in milliseconds, since the session was established.
endpoint_id int
ID of the Endpoint associated with the session.
last_request_start_time datetime
Time at which the last request on the session began. This includes the currently executing request.
last_request_end_time datetime
Time of the last completion of a request on the session.
reads bigint
Number of reads performed, by requests in this session, during this session.
writes bigint
Number of writes performed, by requests in this session, during this session.
logical_reads bigint
Number of logical reads that have been performed on the session.
is_user_process bit
0 if the session is a system session. Otherwise, it is 1.
text_size int
TEXTSIZE setting for the session.
language nvarchar(128)
LANGUAGE setting for the session.
date_format nvarchar(3)
DATEFORMAT setting for the session.
date_first smallint
DATEFIRST setting for the session.
quoted_identifier bit
QUOTED_IDENTIFIER setting for the session.
arithabort bit
ARITHABORT setting for the session.
ansi_null_dflt_on bit
ANSI_NULL_DFLT_ON setting for the session.
ansi_defaults bit
ANSI_DEFAULTS setting for the session.
ansi_warnings bit
ANSI_WARNINGS setting for the session.
ansi_padding bit
ANSI_PADDING setting for the session.
ansi_nulls bit
ANSI_NULLS setting for the session.
concat_null_yields_null bit
CONCAT_NULL_YIELDS_NULL setting for the session.
transaction_isolation_level smallint
Transaction isolation level of the session.
-- 0 = Unspecified
-- 1 = ReadUncomitted
-- 2 = ReadCommitted
-- 3 = Repeatable
-- 4 = Serializable
-- 5 = Snapshot
lock_timeout int
LOCK_TIMEOUT setting for the session. The value is in milliseconds.
deadlock_priority int
DEADLOCK_PRIORITY setting for the session.
row_count bigint
number of rows returned on the session up to this point.
prev_error int
ID of the last error returned on the session.
So that's it for today, the view contains a lot more than I covered. i might expand this if I have time. If you have any suggestions leave me a comment or drop me an Email
The link to the post with all the views is here (as of today this is the only view that is covered)
Dynamic Management Views Blog Post Series Coming To A Screen Near You
Dynamic Management Views Blog Post Series Coming To A Screen Near You
Since I don't always know what to write I decided to give myself enough material for at least 6 months (if I cover 1 view a week). So here it is I will write about the Dynamic Management Views in SQL server 2005. The first view that I will cover is sys.dm_exec_sessions. You can use sys.dm_exec_sessions instead of DBCC USEROPTIONS only it's much better since you can query multiple SPID's
I will have the first post ready about 4:30 PM EST today
Here is a list of all the Dynamic Management Views in SQL Server 2005, I will link the ones that I write about to the post so that you can access all of this from 1 post (in theory)
Common Language Runtime Related Dynamic Management Views
sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies
sys.dm_clr_properties
sys.dm_clr_tasks
Database Mirroring Related Dynamic Management Views
sys.dm_db_mirroring_connections
Database Related Dynamic Management Views
sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_partition_stats
sys.dm_db_task_space_usage
Execution Related Dynamic Management Views and Functions
sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue_stats
sys.dm_exec_cached_plans
sys.dm_exec_connections
sys.dm_exec_cursors
sys.dm_exec_plan_attributes
sys.dm_exec_query_optimizer_info
sys.dm_exec_query_plan
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions added on 2006-08-31
sys.dm_exec_sql_text
Full-Text Search Related Dynamic Management Views
sys.dm_fts_active_catalogs
sys.dm_fts_crawls
sys.dm_fts_crawl_ranges
sys.dm_fts_memory_buffers
sys.dm_fts_memory_pools
Index Related Dynamic Management Views and Functions
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats added on 2006-09-10
I/O Related Dynamic Management Views and Functions
sys.dm_io_backup_tapes
sys.dm_io_cluster_shared_drives
sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
Query Notifications Related Dynamic Management Views
sys.dm_qn_subscriptions
Replication Related Dynamic Management Views
sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
Service Broker Related Dynamic Management Views
sys.dm_broker_activated_tasks
sys.dm_broker_connections
sys.dm_broker_forwarded_messages
sys.dm_broker_queue_monitors
SQL Operating System Related Dynamic Management Views
sys.dm_os_buffer_descriptors
sys.dm_os_memory_pools
sys.dm_os_child_instances
sys.dm_os_performance_counters
sys.dm_os_cluster_nodes
sys.dm_os_schedulers
sys.dm_os_hosts
sys.dm_os_stacks
sys.dm_os_latch_stats
sys.dm_os_sys_info
sys.dm_os_loaded_modules
sys.dm_os_tasks
sys.dm_os_memory_cache_clock_hands
sys.dm_os_threads
sys.dm_os_memory_cache_counters
sys.dm_os_virtual_address_dump
sys.dm_os_memory_cache_entries
sys.dm_os_wait_stats
sys.dm_os_memory_cache_hash_tables
sys.dm_os_waiting_tasks
sys.dm_os_memory_clerks
sys.dm_os_workers
sys.dm_os_memory_objects
Transaction Related Dynamic Management Views and Functions
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store
Since I don't always know what to write I decided to give myself enough material for at least 6 months (if I cover 1 view a week). So here it is I will write about the Dynamic Management Views in SQL server 2005. The first view that I will cover is sys.dm_exec_sessions. You can use sys.dm_exec_sessions instead of DBCC USEROPTIONS only it's much better since you can query multiple SPID's
I will have the first post ready about 4:30 PM EST today
Here is a list of all the Dynamic Management Views in SQL Server 2005, I will link the ones that I write about to the post so that you can access all of this from 1 post (in theory)
Common Language Runtime Related Dynamic Management Views
sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies
sys.dm_clr_properties
sys.dm_clr_tasks
Database Mirroring Related Dynamic Management Views
sys.dm_db_mirroring_connections
Database Related Dynamic Management Views
sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_partition_stats
sys.dm_db_task_space_usage
Execution Related Dynamic Management Views and Functions
sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue_stats
sys.dm_exec_cached_plans
sys.dm_exec_connections
sys.dm_exec_cursors
sys.dm_exec_plan_attributes
sys.dm_exec_query_optimizer_info
sys.dm_exec_query_plan
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions added on 2006-08-31
sys.dm_exec_sql_text
Full-Text Search Related Dynamic Management Views
sys.dm_fts_active_catalogs
sys.dm_fts_crawls
sys.dm_fts_crawl_ranges
sys.dm_fts_memory_buffers
sys.dm_fts_memory_pools
Index Related Dynamic Management Views and Functions
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats added on 2006-09-10
I/O Related Dynamic Management Views and Functions
sys.dm_io_backup_tapes
sys.dm_io_cluster_shared_drives
sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
Query Notifications Related Dynamic Management Views
sys.dm_qn_subscriptions
Replication Related Dynamic Management Views
sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
Service Broker Related Dynamic Management Views
sys.dm_broker_activated_tasks
sys.dm_broker_connections
sys.dm_broker_forwarded_messages
sys.dm_broker_queue_monitors
SQL Operating System Related Dynamic Management Views
sys.dm_os_buffer_descriptors
sys.dm_os_memory_pools
sys.dm_os_child_instances
sys.dm_os_performance_counters
sys.dm_os_cluster_nodes
sys.dm_os_schedulers
sys.dm_os_hosts
sys.dm_os_stacks
sys.dm_os_latch_stats
sys.dm_os_sys_info
sys.dm_os_loaded_modules
sys.dm_os_tasks
sys.dm_os_memory_cache_clock_hands
sys.dm_os_threads
sys.dm_os_memory_cache_counters
sys.dm_os_virtual_address_dump
sys.dm_os_memory_cache_entries
sys.dm_os_wait_stats
sys.dm_os_memory_cache_hash_tables
sys.dm_os_waiting_tasks
sys.dm_os_memory_clerks
sys.dm_os_workers
sys.dm_os_memory_objects
Transaction Related Dynamic Management Views and Functions
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store
Wednesday, August 30, 2006
A SQL Super Hero Is Born
Ken Henderson promised to give away a signed book to the person who made the best Celko superheroTom Øyvind Hogstad has created one on his blog and I think it looks pretty good, even the S in the logo can stay; we can assume it stands for SQL
Below you will find my favorite Celko answer ever
>> how to set the PK in SS Mgmt Studio ? <<
Who cares?? You are not not a real SQL programmer!! You are a "mousey, mousey, click , click" non-programmer. (with a French accent) we spit on you, Video gamer! to be serious, real programmers use a text editor. They know the language they write in. Those stinking "video game tools"slow us down. And they lead us to ask questiosn like this in newsgroups where people liek me will maek fun of you.
You can find more of these Celko ‘answers on Joe Celko The SQL Apprentice
Tuesday, August 29, 2006
Visual Studio 2005 Team Edition For Database Professionals Review On Regdeveloper
The Register has a review on their subsite Regdeveloper about Visual Studio 2005 Team Edition For Database Professionals (also know as Data Dude) There are 3 pictures so that you can see what the tool looks like if you are afraid to install the latest Community Technology Preview on your system( you can get the latest release here )The review is very positive about the tool and this will be one of those tools that you have to have in your shop, just as is the case with SQL Compare and SQL LiteSpeed right now.
There is one little problem with the review in the following sentence: "DD was partially the brainchild of Ewan Garden and Gert Drapers" they managed to spell Euan Garden's name wrong
Read the review here
Monday, August 28, 2006
Round Up Or Down To Nearest Percentage Value By Using FLOOR And CEILING
Let's say you have a value of 13.33 and you want to round this up and down to the nearest .25 in other words for 13.33 you want to display 13.25 and 13.50.How do you do that? It's pretty easy you do FLOOR(Value *4)/4 and CEILING(Value *4)/4
Below are 3 example, 1 for 0.25, 1 for 0.50 and 1 for 0.33
--0.25
DECLARE @Value DECIMAL(10,2)
SET @Value = 13.33
SELECT FLOOR(@Value * 4) / 4.0,CEILING(@Value * 4) / 4.0
GO
--0.50
DECLARE @Value DECIMAL(10,2)
SET @Value = 13.33
SELECT FLOOR(@Value * 2) / 2.0,CEILING(@Value * 2) / 2.0
GO
--0.33
DECLARE @Value DECIMAL(10,2)
SET @Value = 13.36
SELECT FLOOR(@Value * 3) / 3.0,CEILING(@Value * 3) / 3.
Below are 3 example, 1 for 0.25, 1 for 0.50 and 1 for 0.33
--0.25
DECLARE @Value DECIMAL(10,2)
SET @Value = 13.33
SELECT FLOOR(@Value * 4) / 4.0,CEILING(@Value * 4) / 4.0
GO
--0.50
DECLARE @Value DECIMAL(10,2)
SET @Value = 13.33
SELECT FLOOR(@Value * 2) / 2.0,CEILING(@Value * 2) / 2.0
GO
--0.33
DECLARE @Value DECIMAL(10,2)
SET @Value = 13.36
SELECT FLOOR(@Value * 3) / 3.0,CEILING(@Value * 3) / 3.
Friday, August 25, 2006
Win One Of Ken Henderson's SQL Server Guru Books By Creating A PhotoShop Joe Celko Action Figure
That's right you can win one of the following three books
The Guru's Guide to Transact-SQL
The Guru's Guide to SQL Server Architecture and Internals
The Guru's Guide to SQL Server Stored Procedures, XML, and HTML
All you have to do is create a photoshopped depiction of Joe Celko as an action figure.
The best one gets a signed copy of one of his books (too bad I already have all three)
Ken Henderson's Blog
The Guru's Guide to Transact-SQL
The Guru's Guide to SQL Server Architecture and Internals
The Guru's Guide to SQL Server Stored Procedures, XML, and HTML
All you have to do is create a photoshopped depiction of Joe Celko as an action figure.
The best one gets a signed copy of one of his books (too bad I already have all three)
Ken Henderson's Blog
Friday Joke and Frappr Map Update
Since it's Friday today I will keep it light
Let's start with a joke that I read in Wired magazine yesterday
What do you call a blonde who dyes her hair brown?
Artificial intelligence
Next topic
Yesterday I created a Frappr map and asked for you (the reader) to add yourself to the map so that I can see where some of you are located
As of now the countries that are marked are
United States 6 people
Russia 2 people
Canada 1 person
Norway 1 person
So not quite close to my IQ yet
Let's start with a joke that I read in Wired magazine yesterday
What do you call a blonde who dyes her hair brown?
Artificial intelligence
Next topic
Yesterday I created a Frappr map and asked for you (the reader) to add yourself to the map so that I can see where some of you are located
As of now the countries that are marked are
United States 6 people
Russia 2 people
Canada 1 person
Norway 1 person
So not quite close to my IQ yet
Thursday, August 24, 2006
SqlServerCode Frappr Map
I created a Frappr Map and would like for you (yes you the reader) to add yourself to the map because it would be interesting to see where some of the readers of this blog are located
So far I have less members (2) than my IQ, I would like to have more members than my IQ (believe me not a hard task at all)
So this is your chance; beat my IQ
My member name is Denis The SQL Menace
So far I have less members (2) than my IQ, I would like to have more members than my IQ (believe me not a hard task at all)
So this is your chance; beat my IQ
My member name is Denis The SQL Menace
Quest Toad for SQL Server 2.0 Released
Quest Software, Inc. announced the newest version of its award-winning Toad™ database management product family. Toad for SQL Server 2.0 includes new features such as one-of-a-kind integrated debugging, SQL optimization, and integrated comparison and synchronization capabilities for SQL Server 2005. These new features further improve the productivity of SQL Server developers and database administrators (DBAs).New features in version 2.0 of Toad for SQL Server include:
- Database Back-Up Indicator – Demonstrates when it is necessary to back up a system, and integrates seamlessly with LiteSpeed for SQL Server
- SQL Optimization - Automates the process of identifying problematic SQL then re-writing and benchmarking it for optimal SQL Server performance.
- Compare and Sync – Provides advanced integrated comparison and synchronization of schema, data and server settings of two SQL Server environments.
- Debugging – Offers integrated debugging capabilities for SQL Server 2005.
Pricing and Availability
A freeware version of 2.0 of Quest Toad for SQL Server is available now at http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm
A full production version is also available now with North American pricing beginning at $470 per seat (USD). For more information and to download free evaluation copies please visit: http://www.quest.com/toad_for_sql_server/
Tuesday, August 22, 2006
DDL Trigger Events Revisited
Many many moons ago I wrote about DDL Trigger Events
I promised that I would find out all the events that you can combine for example DDL_VIEW_EVENTS instead of CREATE_VIEW
,ALTER_VIEW and DROP_VIEW
So I finally did it, I created a bunch of triggers and then checked with a query that joined sys.triggers and sys.trigger_events
I started by creating a trigger and I used DDL_VIEW_EVENTS
CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO
After that I would check the sys.triggers and sys.trigger_events views to see what was inserted
SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc
In this case 3 rows were inserted
DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW
Then I altered the trigger as follows
CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_USER_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Users!'
ROLLBACK;
GO
I checked again with the same query
SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc
in this case 3 rows were also inserted
DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER
I kept repeating this until I had all the events and below is the result
The number that you see before the event is the type from the sys.trigger_events view
DDL_XML_SCHEMA_COLLECTION_EVENTS
----------------------------------
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION
DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW
DDL_TRIGGER_EVENTS
----------------------------------
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER
DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER
DDL_TYPE_EVENTS
----------------------------------
91 CREATE_TYPE
93 DROP_TYPE
DDL_TABLE_EVENTS
----------------------------------
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE
DDL_SYNONYM_EVENTS
----------------------------------
34 CREATE_SYNONYM
36 DROP_SYNONYM
DDL_STATISTICS_EVENTS
-----------------------------------
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS
DDL_SERVICE_EVENTS
------------------------------------
161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE
DDL_SCHEMA_EVENTS
------------------------------------
141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA
DDL_ROUTE_EVENTS
------------------------------------
164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE
DDL_ROLE_EVENTS
----------------------------------
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE
DDL_REMOTE_SERVICE_BINDING_EVENTS
-----------------------------------
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING
DDL_QUEUE_EVENTS
---------------------------------
157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE
DDL_PROCEDURE_EVENTS
----------------------------------
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE
DDL_PARTITION_SCHEME_EVENTS
------------------------------------
194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME
DDL_PARTITION_FUNCTION_EVENTS
------------------------------
191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION
DDL_EVENT_NOTIFICATION_EVENTS
----------------------------------
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION
DDL_ASSEMBLY_EVENTS
-----------------------------------
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY
DDL_CONTRACT_EVENTS
-----------------------------------
154 CREATE_CONTRACT
156 DROP_CONTRACT
DDL_FUNCTION_EVENTS
---------------------------------
61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION
DDL_INDEX_EVENTS
---------------------------------
24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX
DDL_MESSAGE_TYPE_EVENTS
------------------------------------
151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE
I promised that I would find out all the events that you can combine for example DDL_VIEW_EVENTS instead of CREATE_VIEW
,ALTER_VIEW and DROP_VIEW
So I finally did it, I created a bunch of triggers and then checked with a query that joined sys.triggers and sys.trigger_events
I started by creating a trigger and I used DDL_VIEW_EVENTS
CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO
After that I would check the sys.triggers and sys.trigger_events views to see what was inserted
SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc
In this case 3 rows were inserted
DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW
Then I altered the trigger as follows
CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_USER_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Users!'
ROLLBACK;
GO
I checked again with the same query
SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc
in this case 3 rows were also inserted
DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER
I kept repeating this until I had all the events and below is the result
The number that you see before the event is the type from the sys.trigger_events view
DDL_XML_SCHEMA_COLLECTION_EVENTS
----------------------------------
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION
DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW
DDL_TRIGGER_EVENTS
----------------------------------
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER
DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER
DDL_TYPE_EVENTS
----------------------------------
91 CREATE_TYPE
93 DROP_TYPE
DDL_TABLE_EVENTS
----------------------------------
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE
DDL_SYNONYM_EVENTS
----------------------------------
34 CREATE_SYNONYM
36 DROP_SYNONYM
DDL_STATISTICS_EVENTS
-----------------------------------
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS
DDL_SERVICE_EVENTS
------------------------------------
161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE
DDL_SCHEMA_EVENTS
------------------------------------
141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA
DDL_ROUTE_EVENTS
------------------------------------
164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE
DDL_ROLE_EVENTS
----------------------------------
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE
DDL_REMOTE_SERVICE_BINDING_EVENTS
-----------------------------------
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING
DDL_QUEUE_EVENTS
---------------------------------
157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE
DDL_PROCEDURE_EVENTS
----------------------------------
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE
DDL_PARTITION_SCHEME_EVENTS
------------------------------------
194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME
DDL_PARTITION_FUNCTION_EVENTS
------------------------------
191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION
DDL_EVENT_NOTIFICATION_EVENTS
----------------------------------
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION
DDL_ASSEMBLY_EVENTS
-----------------------------------
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY
DDL_CONTRACT_EVENTS
-----------------------------------
154 CREATE_CONTRACT
156 DROP_CONTRACT
DDL_FUNCTION_EVENTS
---------------------------------
61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION
DDL_INDEX_EVENTS
---------------------------------
24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX
DDL_MESSAGE_TYPE_EVENTS
------------------------------------
151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE
Thursday, August 17, 2006
Architecting Microsoft.com – SQL Server Podcast And Video
Channel 9 has an interesting video and podcast about the microsoft.com architecture with SQL Server
From the site: "At the heart of most web sites once you dig down below the routers, below the web servers, below the ASP.NET code you find a critical piece. The heart of the matter if you will is the database and if that heart stops... well let’s just say your website will assume room temperature quickly. The question is; what do the guys at Microsoft.com do to insure that the heart of the system is thumpin’ and pumpin’ day in and day out 24x7 while still needing to do updates, patches, server maintenance etc.? Well never fear because we have the answer for you so listen up and learn something!"
The podcast is available in WMA and MP3 formats and runs for 27 minutes and 55 seconds
The video length is 26 minutes and 9 seconds
you can get the podcast and the video here (http://channel9.msdn.com/Showpost.aspx?postid=226519)
From the site: "At the heart of most web sites once you dig down below the routers, below the web servers, below the ASP.NET code you find a critical piece. The heart of the matter if you will is the database and if that heart stops... well let’s just say your website will assume room temperature quickly. The question is; what do the guys at Microsoft.com do to insure that the heart of the system is thumpin’ and pumpin’ day in and day out 24x7 while still needing to do updates, patches, server maintenance etc.? Well never fear because we have the answer for you so listen up and learn something!"
The podcast is available in WMA and MP3 formats and runs for 27 minutes and 55 seconds
The video length is 26 minutes and 9 seconds
you can get the podcast and the video here (http://channel9.msdn.com/Showpost.aspx?postid=226519)
Wednesday, August 16, 2006
Query Multiple Databases
"Query Multiple Databases"
This search term has been used 13 times in the last week on this blog. So here is a little blog post about it
It's really not complicated to query multiple databases. You have to have permissions on both databases
The syntax looks like this:
SELECT *
FROM Database1.ObjectOwner.TableName
JOIN Database2.ObjectOwner.TableName ON.....
So here is a little script so that you can test it out
USE master
GO
--Create the first Database
CREATE DATABASE DBtest1
GO
--Create the second Database
CREATE DATABASE DBtest2
GO
USE DBtest1
GO
--Create Table1 on DBtest1
CREATE TABLE Table1(id INT, DescriptionValue VARCHAR(49))
INSERT Table1 VALUES (1,'DBtest1')
GO
USE DBtest2
GO
--Create Table2 on DBtest2
CREATE TABLE Table2(id INT, DescriptionValue VARCHAR(49))
INSERT Table2 VALUES (1,'DBtest2')
GO
USE master
GO
--Do the join between DBtest1 and DBtest2
SELECT *
FROM DBtest1.dbo.Table1 t1
JOIN DBtest2.dbo.Table2 t2 ON t1.id = t2.id
GO
--Another way
SELECT *
FROM DBtest1..Table1 t1
JOIN DBtest2..Table2 t2 ON t1.id = t2.id
GO
--Drop these databases
DROP DATABASE DBtest1,DBtest2
This search term has been used 13 times in the last week on this blog. So here is a little blog post about it
It's really not complicated to query multiple databases. You have to have permissions on both databases
The syntax looks like this:
SELECT *
FROM Database1.ObjectOwner.TableName
JOIN Database2.ObjectOwner.TableName ON.....
So here is a little script so that you can test it out
USE master
GO
--Create the first Database
CREATE DATABASE DBtest1
GO
--Create the second Database
CREATE DATABASE DBtest2
GO
USE DBtest1
GO
--Create Table1 on DBtest1
CREATE TABLE Table1(id INT, DescriptionValue VARCHAR(49))
INSERT Table1 VALUES (1,'DBtest1')
GO
USE DBtest2
GO
--Create Table2 on DBtest2
CREATE TABLE Table2(id INT, DescriptionValue VARCHAR(49))
INSERT Table2 VALUES (1,'DBtest2')
GO
USE master
GO
--Do the join between DBtest1 and DBtest2
SELECT *
FROM DBtest1.dbo.Table1 t1
JOIN DBtest2.dbo.Table2 t2 ON t1.id = t2.id
GO
--Another way
SELECT *
FROM DBtest1..Table1 t1
JOIN DBtest2..Table2 t2 ON t1.id = t2.id
GO
--Drop these databases
DROP DATABASE DBtest1,DBtest2
Monday, August 14, 2006
Analysis Services Stored Procedure Project Released On CodePlex
The Analysis Services Stored Procedure project is a set of sample stored procedures for Analysis Services 2005. These samples have been written in C# and sample MDX queries are included that demonstrate the use of the procedures against the Adventure Works DW sample database. It was developed by a group of community volunteers with 2 main aims in mind:
To provide a set of useful extensions to Analysis Services 2005.
To provide a variety of example source code for people looking to write their own stored procedures.
Visit the CodePlex site to download the source code
To provide a set of useful extensions to Analysis Services 2005.
To provide a variety of example source code for people looking to write their own stored procedures.
Visit the CodePlex site to download the source code
Friday, August 11, 2006
Use The Sieve of Eratosthenes To Find All PrimeNumbers Below 1 Million
In mathematics, the Sieve of Eratosthenes is a simple, ancient algorithm for finding all prime numbers up to a specified integer. It was created by Eratosthenes, an ancient Greek mathematician. Wheel factorization is often applied on the list of integers to be checked for primality, before Sieve of Eratosthenes is used, to increase the speed.
Algorithm
Write a list of numbers from 2 to the largest number you want to test for primality. Call this List A. (This is the list of squares on the left-hand-side of the picture.)
Write the number 2, the first prime number, in another list for primes found. Call this List B. (This is the list on the right-hand-side of the picture.)
Strike off 2 and all multiples of 2 from List A.
The first remaining number in the list is a prime number. Write this number into List B.
Strike off this number and all multiples of this number from List A. The crossing-off of multiples can be started at the square of the number, as lower multiples have already been crossed out in previous steps.
Repeat steps 4 through 6 until no more numbers are left in List A.
Read more on Wikipedia
So here is the SQL Version, it runs in 7 seconds on my machine when I run it a
second time, first run is 16 seconds
Algorithm
Write a list of numbers from 2 to the largest number you want to test for primality. Call this List A. (This is the list of squares on the left-hand-side of the picture.)
Write the number 2, the first prime number, in another list for primes found. Call this List B. (This is the list on the right-hand-side of the picture.)
Strike off 2 and all multiples of 2 from List A.
The first remaining number in the list is a prime number. Write this number into List B.
Strike off this number and all multiples of this number from List A. The crossing-off of multiples can be started at the square of the number, as lower multiples have already been crossed out in previous steps.
Repeat steps 4 through 6 until no more numbers are left in List A.
Read more on Wikipedia
So here is the SQL Version, it runs in 7 seconds on my machine when I run it a
second time, first run is 16 seconds
SET NOCOUNT ON
DECLARE @i INT
-- Create a 10-digit table
DECLARE @D TABLE (N INT)
INSERT INTO @D (N)
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
INSERT INTO @D (N)
SELECT N+5 FROM @D
-- build a small sieve table between 2 and 1000
DECLARE @T TABLE (N INT)
INSERT INTO @T( N )
SELECT 1+A.N+10*(B.N+10*C.N)
FROM @D A, @D B, @D C
DELETE FROM @T WHERE N = 1
SET @I = 2
WHILE @I <= SQRT(1000)
BEGIN
DELETE FROM @T WHERE N % @I = 0 AND N > @I
SET @I = @I + 1
END
-- Create large table between 1001 and 1000000
SELECT A+10*(B+10*(C+10*(D+10*(E+ 10*F)))) AS N
INTO #P
FROM
( SELECT A.N AS A, B.N AS B, C.N AS C, D.N AS D, E.N AS E, F.N AS F
FROM @D A, @D B, @D C, @D D, @D E, @D F
WHERE A.N in (1, 3, 7, 9) -- Not divisible by 2 or 5
) blah
WHERE (A+B+C+D+E+F) % 3 <> 0 -- Or 3
AND (A+3*B+2*C-D-3*E-2*F) % 7 <> 0 -- Or 7
AND (B-A+D-C+F-E) % 11 <> 0 -- Or 11
AND D|E|F <> 0 -- Don't include the first 1000 numbers,
--we already have these in the small sieve table
UNION ALL SELECT 1000000
-- sieve the big table with smaller one
SELECT @I = 2
WHILE @I IS NOT NULL
BEGIN
DELETE FROM #P WHERE N% @I = 0
SELECT @I = MIN(N) FROM @T WHERE N > @I
END
-- add primes up to 1000
INSERT INTO #P SELECT N FROM @T
-- Here are the results
--78498 rows
SELECT * FROM #P ORDER BY 1
drop table #P
go
Wednesday, August 09, 2006
Try Quest LiteSpeed And Win An iPod Nano (Hopefully)
Quest has a nice little give away. They are giving away 40 2GB Apple Nano iPods to people who are willing to try LiteSpeed for SQL Server. I am already using LiteSpeed on the production machines but the cool thing is that Quest has a free version that you can use with the developer editions of SQL Server at no cost at all
So check it out here
Monday, August 07, 2006
SQL Server Book Sales Up 98% Compared To Last Year
Tim O’Reilly has posted his latest State of the Computer Book Market article. Here are 2 excerpts: “Looking at the Database rollup, we again see the strength of SQL Server, the decline of Oracle book sales, and that while MySQL is still a much larger category than Postgres, Postgres is showing some curious strength. This is one of the things that treemap visualizations are good for. Small, bright green categories stand out, and you can start paying closer attention. (Ruby also showed bright green while it was still a tiny category before its remarkable surge over the past year.) We also see the continuing popularity of personal databases like Access and Filemaker.
“
“A few high-level observations:
• C# book sales continues to gain on Java, with a 49% unit sales increase compared to Java's 10% decrease.
• Ruby continues its momentum, and is the fastest-growing programming language in terms of book sales.
• Microsoft's new release of SQL Server has continued to drive significant book sales, with that market up 86%. ASP.Net is also on a roll, with book sales up 61%.
“
Read the whole article here
“
“A few high-level observations:
• C# book sales continues to gain on Java, with a 49% unit sales increase compared to Java's 10% decrease.
• Ruby continues its momentum, and is the fastest-growing programming language in terms of book sales.
• Microsoft's new release of SQL Server has continued to drive significant book sales, with that market up 86%. ASP.Net is also on a roll, with book sales up 61%.
“
Read the whole article here
Thursday, August 03, 2006
Store The Output Of A Stored Procedure In A Table Without Creating A Table
I saw this technique in the Inside Microsoft SQL Server 2005: T-SQL Querying T-SQL and today I saw it also here The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement @ Ward Pond's SQL Server blog
I decided to expand this a little
Sometimes I need to quickly find out who is blocking what (or what is blocking who for that matter) so I run sp_who2 then look at the BlkBy column and run a DBCC INPUTBUFFER or fn_get_sql to get the sql statement
When you have a lot of connections it's a pain in the neck to look for the BlkBy where it's not . since the result is ordered by SPID
You can always do
CREATE TABLE
INSERT TABLE
EXEC Proc
But who wants to create tables all the time (not me, at least not for this stuff)
SPID is twice in the resultset of sp_who2 (who know why?) so that complicates things a little
So let's start with sp_who
SELECT * INTO #TempSpWho
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
--return only the connections to master
SELECT * FROM #TempSpWho
WHERE dbname ='master'
--Let's try sp_who2
SELECT * INTO #TempSpWho2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')
--Oops
Server: Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'SPID' in table '#TempSpWho2' is specified more than once.
--No problem list the columns
SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName
INTO #TempSpWho2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')
--Get the connections to master only
SELECT * FROM #TempSpWho2
WHERE dbname ='master'
--Get the blocking SPID's
SELECT * FROM #TempSpWho2
WHERE BlkBy NOT LIKE '% .'
--Get user connections only (everything with a SPID below 51 is reserved for sql server processes)
SELECT * FROM #TempSpWho2
WHERE SPID >= 50
A couple of notes
The OPENROWSET call opens a separate connection to SQL Server, so there is some overhead associated with this approach
This technique is not supported inside a declared transaction
Blocking can occure with a poorly architected stored procedure
I decided to expand this a little
Sometimes I need to quickly find out who is blocking what (or what is blocking who for that matter) so I run sp_who2 then look at the BlkBy column and run a DBCC INPUTBUFFER or fn_get_sql to get the sql statement
When you have a lot of connections it's a pain in the neck to look for the BlkBy where it's not . since the result is ordered by SPID
You can always do
CREATE TABLE
INSERT TABLE
EXEC Proc
But who wants to create tables all the time (not me, at least not for this stuff)
SPID is twice in the resultset of sp_who2 (who know why?) so that complicates things a little
So let's start with sp_who
SELECT * INTO #TempSpWho
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
--return only the connections to master
SELECT * FROM #TempSpWho
WHERE dbname ='master'
--Let's try sp_who2
SELECT * INTO #TempSpWho2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')
--Oops
Server: Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'SPID' in table '#TempSpWho2' is specified more than once.
--No problem list the columns
SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName
INTO #TempSpWho2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')
--Get the connections to master only
SELECT * FROM #TempSpWho2
WHERE dbname ='master'
--Get the blocking SPID's
SELECT * FROM #TempSpWho2
WHERE BlkBy NOT LIKE '% .'
--Get user connections only (everything with a SPID below 51 is reserved for sql server processes)
SELECT * FROM #TempSpWho2
WHERE SPID >= 50
A couple of notes
The OPENROWSET call opens a separate connection to SQL Server, so there is some overhead associated with this approach
This technique is not supported inside a declared transaction
Blocking can occure with a poorly architected stored procedure
Wednesday, August 02, 2006
Top SQL Server Google Searches For July 2006
These are the top SQL Searches on this site for the month of July. I have left out searches that have nothing to do with SQL Server or programming. As you can see it has been a slow(er) month. And Google reports only searches that have been searched for more than once. For example if you have these 4 searches
insert triggers
insert trigger
insert triggers
insert trigger!
Then only insert triggers will show up since the other two were used only one time
Here are the results...
increase maximum row size in mssql
crystal report division by zero code
insert triggers
select sum where
select from
delete a null record
Let's talk about the last search (delete a null record ) a little bit
The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL
CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(NULL)
--Check what's in the table
SELECT * FROM #TestDeleteNull
--This won't work
DELETE #TestDeleteNull
WHERE SomeDate = NULL
--(0 row(s) affected)
--This is the way
DELETE #TestDeleteNull
WHERE SomeDate IS NULL
--(1 row(s) affected)
--Check again
SELECT * FROM #TestDeleteNull
insert triggers
insert trigger
insert triggers
insert trigger!
Then only insert triggers will show up since the other two were used only one time
Here are the results...
increase maximum row size in mssql
crystal report division by zero code
insert triggers
select sum where
select from
delete a null record
Let's talk about the last search (delete a null record ) a little bit
The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL
CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(NULL)
--Check what's in the table
SELECT * FROM #TestDeleteNull
--This won't work
DELETE #TestDeleteNull
WHERE SomeDate = NULL
--(0 row(s) affected)
--This is the way
DELETE #TestDeleteNull
WHERE SomeDate IS NULL
--(1 row(s) affected)
--Check again
SELECT * FROM #TestDeleteNull
Tuesday, August 01, 2006
6 Different Ways To Get The Current Identity Value From A Table
This article will show you how to get the current identity value from a table and also some things that might act a little different than you would expect
Let's first create our two simple tables
CREATE TABLE TestOne (id INT identity,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT identity,TestOneID INT,SomeDate DATETIME)
--Let's insert 4 rows into the table
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
Here are 6 ways to check for the current value
--1 @@IDENTITY
SELECT @@IDENTITY
--this returns 4
--2 DBCC CHECKIDENT
DBCC CHECKIDENT (TestOne, NORESEED)
after running DBCC CHECKIDENT the message returned is
Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--3 MAX function
SELECT MAX(id)
FROM TestOne
you can also check with the MAX function but this is not recommended becuase you might get some other identity value that is not yours but from a different user
--4 TOP 1 and ORDER BY DESC
SELECT TOP 1 id
FROM TestOne
ORDER BY id DESC
--The same applies here as for the max function, this is not recommended
--5 IDENT_CURRENT
SELECT IDENT_CURRENT('TestOne')
--IDENT_CURRENT is another way to check
--6 SCOPE_IDENTITY
SELECT SCOPE_IDENTITY()
--This one is very similar to @@IDENTITY with one BIG difference (shown later)
--Let's add a trigger to the TestOne table
CREATE TRIGGER trTestOne ON [dbo].[TestOne]
FOR INSERT
AS
DECLARE @CreditUserID INT
SELECT @CreditUserID = (SELECT ID FROM Inserted)
INSERT TestTwo VALUES(@CreditUserID,GETDATE())
GO
--Let's insert another row into the TestOne table
INSERT TestOne VALUES(GETDATE())
SELECT @@IDENTITY --1
SELECT @@IDENTITY,SCOPE_IDENTITY(),IDENT_CURRENT('TestOne') --1,5,5
Now why doesn't @@IDENTITY return 5 but 1?
This is because @@IDENTITY doesn't care about scope and returns the last identity value from all the statements, which in this case is from the code within the trigger trTestOne
So the bottom line is this: Always use SCOPE_IDENTITY() unless you DO need the last identity value regradless of scope (for example you need to know the identity from the table insert inside the trigger)
--Clean up this mess
DROP TABLE TestOne,TestTwo
Let's first create our two simple tables
CREATE TABLE TestOne (id INT identity,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT identity,TestOneID INT,SomeDate DATETIME)
--Let's insert 4 rows into the table
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
Here are 6 ways to check for the current value
--1 @@IDENTITY
SELECT @@IDENTITY
--this returns 4
--2 DBCC CHECKIDENT
DBCC CHECKIDENT (TestOne, NORESEED)
after running DBCC CHECKIDENT the message returned is
Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--3 MAX function
SELECT MAX(id)
FROM TestOne
you can also check with the MAX function but this is not recommended becuase you might get some other identity value that is not yours but from a different user
--4 TOP 1 and ORDER BY DESC
SELECT TOP 1 id
FROM TestOne
ORDER BY id DESC
--The same applies here as for the max function, this is not recommended
--5 IDENT_CURRENT
SELECT IDENT_CURRENT('TestOne')
--IDENT_CURRENT is another way to check
--6 SCOPE_IDENTITY
SELECT SCOPE_IDENTITY()
--This one is very similar to @@IDENTITY with one BIG difference (shown later)
--Let's add a trigger to the TestOne table
CREATE TRIGGER trTestOne ON [dbo].[TestOne]
FOR INSERT
AS
DECLARE @CreditUserID INT
SELECT @CreditUserID = (SELECT ID FROM Inserted)
INSERT TestTwo VALUES(@CreditUserID,GETDATE())
GO
--Let's insert another row into the TestOne table
INSERT TestOne VALUES(GETDATE())
SELECT @@IDENTITY --1
SELECT @@IDENTITY,SCOPE_IDENTITY(),IDENT_CURRENT('TestOne') --1,5,5
Now why doesn't @@IDENTITY return 5 but 1?
This is because @@IDENTITY doesn't care about scope and returns the last identity value from all the statements, which in this case is from the code within the trigger trTestOne
So the bottom line is this: Always use SCOPE_IDENTITY() unless you DO need the last identity value regradless of scope (for example you need to know the identity from the table insert inside the trigger)
--Clean up this mess
DROP TABLE TestOne,TestTwo
Wednesday, July 26, 2006
@@ROWCOUNT And Trigger Woes
Here I go again linking to other people's posts instead of writing my own ;-(
Louis Davidson has a nice little entry on his SQL Doctor blog about Triggers and the @@ROWCOUNT function
You can check it out here
Louis Davidson has a nice little entry on his SQL Doctor blog about Triggers and the @@ROWCOUNT function
You can check it out here
Tuesday, July 25, 2006
Integer Math In SQL Server
If you divide with 2 integers in SQL Server the result will be also an integer( 3/2 =1, 5/2 =2)
If you need the fractions then you need to convert/cast to decimal/numeric or multiply one of the integers by 1.0
Let's test this out
DECLARE @Value1 INT
DECLARE @Value2 INT
SELECT @Value1 =3,@Value2 =2
--here is where we get 1 instead of 1.5
SELECT @Value1/@Value2
--after using convert the result is correct
SELECT @Value1/CONVERT(DECIMAL(3,2),@Value2)
--after using cast the result is correct
SELECT @Value1/CAST(@Value2 AS DECIMAL(3,2))
--after multiplying with 1.0 the result is correct
SELECT @Value1/(@Value2*1.0)
--this won't work since the multiplication is done after we have the result
SELECT @Value1/@Value2*1.0
If you need the fractions then you need to convert/cast to decimal/numeric or multiply one of the integers by 1.0
Let's test this out
DECLARE @Value1 INT
DECLARE @Value2 INT
SELECT @Value1 =3,@Value2 =2
--here is where we get 1 instead of 1.5
SELECT @Value1/@Value2
--after using convert the result is correct
SELECT @Value1/CONVERT(DECIMAL(3,2),@Value2)
--after using cast the result is correct
SELECT @Value1/CAST(@Value2 AS DECIMAL(3,2))
--after multiplying with 1.0 the result is correct
SELECT @Value1/(@Value2*1.0)
--this won't work since the multiplication is done after we have the result
SELECT @Value1/@Value2*1.0
Return All the Rows From A Table Where At Least One Of The Columns IS NULL
Sometimes you inherit a table/database and you need to check if any of the rows in the table have columns with null values
I will show you a great timesaver to accomplish this task
First let's create the table
CREATE TABLE TestNulls (c1 INT,c2 INT, c3 INT,c4 INT,c5 INT, c6 INT)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,NULL,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,NULL,1,1)
One way to check is to write an OR for every column
SELECT *
FROM TestNulls
WHERE C1 IS NULL
OR C2 IS NULL
OR C3 IS NULL
OR C4 IS NULL
OR C5 IS NULL
OR C6 IS NULL
A better (faster) way is to do it like this
SELECT *
FROM TestNulls
WHERE C1+C2+C3+C4+C5+C6 IS NULL
What if you have 50 columns, who wants to write them all out? Well not me.
No problem in Query Analyzer hit F8, this will bring up the Object Browser
In SQL Server Managment Studio use the Object Explorer
Navigate to the table click on the + and drag the whole column folder into the query window
You will see something like this c1, c2, c3, c4, c5, c6
Copy that open up notepad and paste it into notepad. Hit CTRL + H and in the Find box type a , and in the Replace box type a +, hit OK. After that do a CTRL + A and CTRL + C
Paste it back into Query Analyzer/SQL Server Managment Studio and you are done with that part
This will work everytime if all the columns are integers, what if you have varchars?
Let's test that out
CREATE TABLE TestNullsChar (c1 CHAR(1),c2 CHAR(1), c3 CHAR(1),c4 CHAR(1),c5 CHAR(1), c6 CHAR(1))
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,NULL,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,NULL,1,1)
No problem here (depending on your CONCAT_NULL_YIELDS_NULL setting)
SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL
Let's set our CONCAT_NULL_YIELDS_NULL setting to off
SET CONCAT_NULL_YIELDS_NULL OFF
Run the query again
SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL
As you can see nothing was returned so make sure that CONCAT_NULL_YIELDS_NULL is set to on before executing such a query because NULL + any other value will return NULL if CONCAT_NULL_YIELDS_NULL is set to on
SET CONCAT_NULL_YIELDS_NULL ON
I will show you a great timesaver to accomplish this task
First let's create the table
CREATE TABLE TestNulls (c1 INT,c2 INT, c3 INT,c4 INT,c5 INT, c6 INT)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,NULL,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,NULL,1,1)
One way to check is to write an OR for every column
SELECT *
FROM TestNulls
WHERE C1 IS NULL
OR C2 IS NULL
OR C3 IS NULL
OR C4 IS NULL
OR C5 IS NULL
OR C6 IS NULL
A better (faster) way is to do it like this
SELECT *
FROM TestNulls
WHERE C1+C2+C3+C4+C5+C6 IS NULL
What if you have 50 columns, who wants to write them all out? Well not me.
No problem in Query Analyzer hit F8, this will bring up the Object Browser
In SQL Server Managment Studio use the Object Explorer
Navigate to the table click on the + and drag the whole column folder into the query window
You will see something like this c1, c2, c3, c4, c5, c6
Copy that open up notepad and paste it into notepad. Hit CTRL + H and in the Find box type a , and in the Replace box type a +, hit OK. After that do a CTRL + A and CTRL + C
Paste it back into Query Analyzer/SQL Server Managment Studio and you are done with that part
This will work everytime if all the columns are integers, what if you have varchars?
Let's test that out
CREATE TABLE TestNullsChar (c1 CHAR(1),c2 CHAR(1), c3 CHAR(1),c4 CHAR(1),c5 CHAR(1), c6 CHAR(1))
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,NULL,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,NULL,1,1)
No problem here (depending on your CONCAT_NULL_YIELDS_NULL setting)
SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL
Let's set our CONCAT_NULL_YIELDS_NULL setting to off
SET CONCAT_NULL_YIELDS_NULL OFF
Run the query again
SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL
As you can see nothing was returned so make sure that CONCAT_NULL_YIELDS_NULL is set to on before executing such a query because NULL + any other value will return NULL if CONCAT_NULL_YIELDS_NULL is set to on
SET CONCAT_NULL_YIELDS_NULL ON
Monday, July 24, 2006
Undocumented Options For UPDATE STATISTICS
Since I am still sleep deprived and can't think straight I have decided to post a link to a good article on the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog about some undocumented options that can be used with UPDATE STATISTICS
The link to that post is here: UPDATE STATISTICS Undocumented Options
The link to that post is here: UPDATE STATISTICS Undocumented Options
Wednesday, July 19, 2006
A Father Again
I am a father again; my wife gave birth to 2 beautiful babies on Monday night. The boy is named Nicholas and the girl is name Catherine (yes just like Catherine the Great and Czar Nicholas II) I was working Monday and knew we were launching some new products this Wednesday. So I did all the work on staging on Monday and said to myself I will put it on the production box on Tuesday just in case my wife goes into labor. Well my wife didn’t feel that good on Monday and went in for a checkup and they decided to deliver the babies the same day. My son was with my wife so the person who I report to drove me to the hospital (I did not bring my keys to work) after that he drove me home so I could get some stuff and then he drove me back again (who else has a boss like that? ) A friend of ours came to the hospital to watch our son because my wife insisted that I had to be in the delivery room. My wife’s sisters were on vacation in Ocean City, they decided to visit that night and drove to the hospital. They arrived during the time that the babies were born. After the babies were born the sister took our son to our home and stayed there and I slept on the most comfortable (yeah right) hospital pull out chair. The sisters left on Tuesday and I went back home at 9PM. After putting my son to bed I logged in to my computer at work and worked for about an hour to move all the code over and to test that I didn’t mess up anything. My wife is still in the hospital but she will be coming home with the babies Friday morning. That’s it for now, next post will be SQL related again, probably sometime next week.
Below are some pictures, click on the pictures to get a bigger picture
Susan, Christian, Catherine and Nicholas
Nicholas
Catherine
Below are some pictures, click on the pictures to get a bigger picture
Susan, Christian, Catherine and Nicholas
Nicholas
Catherine
Monday, July 17, 2006
Flat File Bulk Import Speed Comparison In SQL Server 2005
Mladen has compared the following 4 methods for importing a flat file in SQl Server 2005
1. BCP
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS
Which do you think is the fastest? If you answered 1 or 2 you might be in for a surprise. Visit Mladen's blog to find out the answer
1. BCP
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS
Which do you think is the fastest? If you answered 1 or 2 you might be in for a surprise. Visit Mladen's blog to find out the answer
Three Ways To Get The Time Portion Of A Datetime Value
This popped up in a newsgroup today so I decided to do a little post about it. How do you get the time information only from a datetime value
These are at least 3 ways to do this
1) use the 3 different datepart functions and concatenate them
2) convert and grab the 8 right most characters
3) convert to varchar and use style 108
So here we go
--1 use the 3 different datepart functions and concatenate them
SELECT CONVERT(VARCHAR(2),DATEPART(HH,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(MI,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(S,GETDATE()))
--2 convert and grab the 8 right most characters
SELECT RIGHT(CONVERT(VARCHAR(22),GETDATE(),120),8)
--3 convert to varchar and use style 108
SELECT CONVERT(VARCHAR(12),GETDATE(),108)
These are at least 3 ways to do this
1) use the 3 different datepart functions and concatenate them
2) convert and grab the 8 right most characters
3) convert to varchar and use style 108
So here we go
--1 use the 3 different datepart functions and concatenate them
SELECT CONVERT(VARCHAR(2),DATEPART(HH,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(MI,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(S,GETDATE()))
--2 convert and grab the 8 right most characters
SELECT RIGHT(CONVERT(VARCHAR(22),GETDATE(),120),8)
--3 convert to varchar and use style 108
SELECT CONVERT(VARCHAR(12),GETDATE(),108)
Thursday, July 13, 2006
Visual Studio 2005 Team Edition for Database Professionals Interview On Channel 9
Channel 9 has a cool webcast with several people talking about Visual Studio 2005 Team Edition for Database Professionals (Data Dude)
From the site: "Cameron Skinner, Gert Drapers, Robert Merriman, Thomas Murphy, and Matt Nunn sat down me with to discuss a new edition to the Visual Studio Team product family: Visual Studio 2005 Team Edition for Database Professionals. We talked about why this product was created and Cameron (the product unit manager) gave a walk through of the current CTP release. Targeted for delivery at the end of the year, you can grab the CTP bits and learn more at the team’s website: http://msdn.microsoft.com/vstudio/teamsystem/dbpro/"
You can download the webcast here (http://channel9.msdn.com/Showpost.aspx?postid=212797) or click on the picture
Visual Studio 2005 Team Edition for Database Professionals CTP 4 Available
Visual Studio 2005 Team Edition for Database Professionals (Data Dude) CTP 4 is available for download
Brief Description
Community Technology Preview (CTP) 4
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development.
Overview
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development. This release is focused on completing a full functionality scenario for SQL 2000 and includes an updated project system with reverse engineering, a new SQL Query Editor, Schema and Data Compare, Data Generation, Unit Testing, and Rename Refactoring. This release does not have functionality changes from CTP3, only stability work and the new project UI.
Get it here
Brief Description
Community Technology Preview (CTP) 4
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development.
Overview
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development. This release is focused on completing a full functionality scenario for SQL 2000 and includes an updated project system with reverse engineering, a new SQL Query Editor, Schema and Data Compare, Data Generation, Unit Testing, and Rename Refactoring. This release does not have functionality changes from CTP3, only stability work and the new project UI.
Get it here
Wednesday, July 12, 2006
Tuesday, July 11, 2006
Non Updating Update Performance Improvement In SQL Server 2005
When you do a non updating update like the one below
UPDATE t
SET i = 1
WHERE i=1
In SQL 2000, the lack of the optimization leads to updating the nonclustered index even if the value is not changing.
In the SQL 2005 plan, it is possible to appreciate
- a “Compute Scalar” operator that compares the current value and new value of the column being modified
- a new filter operator that on a row by row basis will determine whether the nonclustered index is being affected or not
- the fact that nonclustered index maintenance is now bypassed
Read the whole article (including statistics profile output screenshot) at the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog
UPDATE t
SET i = 1
WHERE i=1
In SQL 2000, the lack of the optimization leads to updating the nonclustered index even if the value is not changing.
In the SQL 2005 plan, it is possible to appreciate
- a “Compute Scalar” operator that compares the current value and new value of the column being modified
- a new filter operator that on a row by row basis will determine whether the nonclustered index is being affected or not
- the fact that nonclustered index maintenance is now bypassed
Read the whole article (including statistics profile output screenshot) at the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog
Monday, July 10, 2006
Setting Identity Value Back To 1 After Deleting All Rows From A Table
Sometimes you want the identity value to start from 1 again after you delete all the rows from a table
There are 2 ways to accomplish that
1 Use the TRUNCATE TABLE command instead of DELETE
2 If you do use DELETE then run DBCC CHECKIDENT and reseed the table
Here is some code to explain what I mean
CREATE TABLE TestValues (Id INT identity,DateVale DATETIME)
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- This is the output
-- 1 2006-07-10 12:31:17.050
-- 2 2006-07-10 12:31:17.050
-- 3 2006-07-10 12:31:17.050
-- 4 2006-07-10 12:31:17.050
-- 5 2006-07-10 12:31:17.050
DELETE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 6 2006-07-10 12:31:29.143
TRUNCATE TABLE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:38.317
INSERT INTO TestValues VALUES(GETDATE())
DELETE TestValues
DBCC CHECKIDENT(TestValues,RESEED,0)
-- Message
-- Checking identity information: current identity value '2', current column value '0'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:52.503
DROP TABLE TestValues
There are 2 ways to accomplish that
1 Use the TRUNCATE TABLE command instead of DELETE
2 If you do use DELETE then run DBCC CHECKIDENT and reseed the table
Here is some code to explain what I mean
CREATE TABLE TestValues (Id INT identity,DateVale DATETIME)
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- This is the output
-- 1 2006-07-10 12:31:17.050
-- 2 2006-07-10 12:31:17.050
-- 3 2006-07-10 12:31:17.050
-- 4 2006-07-10 12:31:17.050
-- 5 2006-07-10 12:31:17.050
DELETE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 6 2006-07-10 12:31:29.143
TRUNCATE TABLE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:38.317
INSERT INTO TestValues VALUES(GETDATE())
DELETE TestValues
DBCC CHECKIDENT(TestValues,RESEED,0)
-- Message
-- Checking identity information: current identity value '2', current column value '0'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:52.503
DROP TABLE TestValues
Building SQL Server Integration Services Packages
Learn how easy it is to build and debug your first SQL Server Integration Services (SSIS) Package. Donald Farmer introduces the development environment and the basic concepts of packages and walks through the creation and testing of a simple data flow. See why users describe SSIS as the most productive environment for data integration.
Watch the video at MSDN TV
Get the files here
Watch the video at MSDN TV
Get the files here
Wednesday, July 05, 2006
Designing Effective Aggregations In Analysis Services 2005
If you're into Analysis Services, Integration Services, Reporting Services and BI in general - you'll want to check out Elizabeth Vitt's new blog I found the link to her blog on Kimberly Tripps blog
In her first entry, Elizabeth Vitt highlights the design components that determine which attributes are considered for aggregation:
Aggregation Definition
Attribute Relationships
Aggregation Candidates
Aggregation Usage
Read the article here: Influencing Aggregation Candidates
In her first entry, Elizabeth Vitt highlights the design components that determine which attributes are considered for aggregation:
Aggregation Definition
Attribute Relationships
Aggregation Candidates
Aggregation Usage
Read the article here: Influencing Aggregation Candidates
Tuesday, July 04, 2006
Free Quest LiteSpeed For SQL Server Developer Edition
Here is an Independence Day gift for you. You can get the fast backup and recovery solution LiteSpeed for free (to be used with the developer edition only)From the Quest site:
"The fast backup and recovery solution for Microsoft SQL Server Developer Edition is here - LiteSpeed™ for SQL Server Developer Edition. LiteSpeed's low-impact, high-performance compression technology allows you to dramatically reduce storage costs and backup/recovery windows.
With LiteSpeed, you'll achieve:
Fast backup and recovery. LiteSpeed's technology achieves 50 percent faster backup and recovery times, allowing you to cut your restore time in half.
Reduced file sizes and storage costs. LiteSpeed's compression technology compresses data up to 95 percent, saving disk space.
Optimized backup and recovery. You'll maintain complete control, while backup and recovery performance is improved right out of the box.
Reduce the time and costs associated with backup and recovery projects today with this full-version download. LiteSpeed for SQL Server Developer Edition - a $45 value - is completely FREE to SQL Server Central readers for a limited time"
Get it here
Monday, July 03, 2006
DDL Triggers And Events
I promised I would write a 'real' SQl subject so here it is: DDL triggers and DDL events for use with DDL triggers
Let's start by creating 2 simple triggers
USE AdventureWorks
GO
CREATE TRIGGER ddlTestEvents1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
PRINT 'You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!'
ROLLBACK;
GO
CREATE TRIGGER ddlTestEvents2
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!'
ROLLBACK;
GO
Let's try creating a table
CREATE TABLE wasabi(id INT)
GO
And here is the error message, the first trigger fired
You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Let's disable the ddlTestEvents1 trigger and see what happens
DISABLE TRIGGER [ddlTestEvents1] ON DATABASE
GO
Let's try creating a table again
CREATE TABLE wasabi(id INT)
GO
Aha, same error message(almost) different trigger
You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
so basically the event DDL_TABLE_EVENTS is the same as the DROP_TABLE, ALTER_TABLE, CREATE_TABLE events. To check that run the following code
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc
The ouput is the same for both triggers
name...........,type_desc,type
ddlTestEvents1,ALTER_TABLE,22
ddlTestEvents1, CREATE_TABLE,21
ddlTestEvents1, DROP_TABLE, 23
ddlTestEvents2, ALTER_TABLE, 22
ddlTestEvents2, CREATE_TABLE, 21
ddlTestEvents2, DROP_TABLE, 23
So when you use DDL_TABLE_EVENTS in the trigger, then 3 events will be in the sys.trigger_events catalog view. This is all fine but how do you find out these things? I can't find DDL_TABLE_EVENTS anywhere. Also I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?
Script out the ddlDatabaseTriggerLog trigger (this is the only trigger that is in the AdventureWorks database when you install SQL server), you will see a DDL_DATABASE_LEVEL_EVENTS event
when you run the following query (same as before, different trigger name) you will see that it returns 76 rows (basically all events)
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name ='ddlDatabaseTriggerLog'
ORDER BY name,te.type_desc
I am sure I will edit this (many times)
But first I will list all the events
You will see the events followed by a grouped event in red for example
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
I will have to go through all these events and use the sys.trigger_events to find this info out before I post this (unless someone points to in the right direction where to find this)
DDL Statements with Database Scope:
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
-- should be DDL_ASSEMBLY_EVENTS but who knows?
ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE
CREATE_CONTRACT
DROP_CONTRACT
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
ALTER_ROLE
DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
DROP_SCHEMA
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)
DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)
CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
ALTER_USER
DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION
DDL Statements with Server Scope:
ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
CREATE_ENDPOINT
ALTER_ENDPOINT
DROP_ENDPOINT
CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)
ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
DDL_LOGIN_EVENTS
GRANT_SERVER
DENY_SERVER
REVOKE_SERVER
And like I said I will edit this after I run my tests
Let's start by creating 2 simple triggers
USE AdventureWorks
GO
CREATE TRIGGER ddlTestEvents1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
PRINT 'You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!'
ROLLBACK;
GO
CREATE TRIGGER ddlTestEvents2
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!'
ROLLBACK;
GO
Let's try creating a table
CREATE TABLE wasabi(id INT)
GO
And here is the error message, the first trigger fired
You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Let's disable the ddlTestEvents1 trigger and see what happens
DISABLE TRIGGER [ddlTestEvents1] ON DATABASE
GO
Let's try creating a table again
CREATE TABLE wasabi(id INT)
GO
Aha, same error message(almost) different trigger
You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
so basically the event DDL_TABLE_EVENTS is the same as the DROP_TABLE, ALTER_TABLE, CREATE_TABLE events. To check that run the following code
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc
The ouput is the same for both triggers
name...........,type_desc,type
ddlTestEvents1,ALTER_TABLE,22
ddlTestEvents1, CREATE_TABLE,21
ddlTestEvents1, DROP_TABLE, 23
ddlTestEvents2, ALTER_TABLE, 22
ddlTestEvents2, CREATE_TABLE, 21
ddlTestEvents2, DROP_TABLE, 23
So when you use DDL_TABLE_EVENTS in the trigger, then 3 events will be in the sys.trigger_events catalog view. This is all fine but how do you find out these things? I can't find DDL_TABLE_EVENTS anywhere. Also I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?
Script out the ddlDatabaseTriggerLog trigger (this is the only trigger that is in the AdventureWorks database when you install SQL server), you will see a DDL_DATABASE_LEVEL_EVENTS event
when you run the following query (same as before, different trigger name) you will see that it returns 76 rows (basically all events)
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name ='ddlDatabaseTriggerLog'
ORDER BY name,te.type_desc
I am sure I will edit this (many times)
But first I will list all the events
You will see the events followed by a grouped event in red for example
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
I will have to go through all these events and use the sys.trigger_events to find this info out before I post this (unless someone points to in the right direction where to find this)
DDL Statements with Database Scope:
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
-- should be DDL_ASSEMBLY_EVENTS but who knows?
ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE
CREATE_CONTRACT
DROP_CONTRACT
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
ALTER_ROLE
DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
DROP_SCHEMA
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)
DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)
CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
ALTER_USER
DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION
DDL Statements with Server Scope:
ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
CREATE_ENDPOINT
ALTER_ENDPOINT
DROP_ENDPOINT
CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)
ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
DDL_LOGIN_EVENTS
GRANT_SERVER
DENY_SERVER
REVOKE_SERVER
And like I said I will edit this after I run my tests
Top 5 Posts For June 2006
Below are the top 5 posts according to Google Analytics for the month of June
COALESCE And ISNULL Differences is number one, the big reason for this is that SQL Server Magazine linked to it
Here are the posts in order by pageviews descending
COALESCE And ISNULL Differences
OPENROWSET And Excel Problems
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Split a comma delimited string fast!
SQL Query Optimizations
And I promise I will have some real SQL code later today
COALESCE And ISNULL Differences is number one, the big reason for this is that SQL Server Magazine linked to it
Here are the posts in order by pageviews descending
COALESCE And ISNULL Differences
OPENROWSET And Excel Problems
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Split a comma delimited string fast!
SQL Query Optimizations
And I promise I will have some real SQL code later today
Sunday, July 02, 2006
Top SQL Server Google Searches For June 2006
These are the top SQL Searches on this site for the month of June. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
dbreindex microsoft sqlserver forum
mysql grant privileges column level
site:sqlservercode.blogspot.com union
-310 sql error
"SQL Server Everywhere"
Collation
replace last two characters
grant update mysql syntax
"The provider ran out of memory"
money
-310 sql
how to sql to excel
Msg 2627, Level 14, State 1
ADD_DAYS oracle syntax
query for SQL bit checking
dbreindex microsoft sqlserver forum
mysql grant privileges column level
site:sqlservercode.blogspot.com union
-310 sql error
"SQL Server Everywhere"
Collation
replace last two characters
grant update mysql syntax
"The provider ran out of memory"
money
-310 sql
how to sql to excel
Msg 2627, Level 14, State 1
ADD_DAYS oracle syntax
query for SQL bit checking
Thursday, June 29, 2006
List DDL Triggers By Using The sys.triggers Catalog View
How do you list DDL triggers in SQL Server 2005? It's very easy you just query the sys.triggers catalog view. Let's try an example
USE adventureworks
GO
--Create our trigger
CREATE TRIGGER trDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You can not drop tables'
ROLLBACK
GO
--Create the table
CREATE TABLE abc(id INT)
GO
--Let's drop the table
DROP TABLE abc
GO
Our trigger works because the following message is displayed after we tried to drop the table
You can not drop tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
In order to list DDL triggers you need to use parent_id = 0
SELECT * FROM sys.triggers
WHERE parent_id = 0
In order to list DML triggers you need to use parent_id <> 0
SELECT * FROM sys.triggers
WHERE parent_id <> 0
And if you want to list all triggers you can just ommit the WHERE clause
USE adventureworks
GO
--Create our trigger
CREATE TRIGGER trDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You can not drop tables'
ROLLBACK
GO
--Create the table
CREATE TABLE abc(id INT)
GO
--Let's drop the table
DROP TABLE abc
GO
Our trigger works because the following message is displayed after we tried to drop the table
You can not drop tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
In order to list DDL triggers you need to use parent_id = 0
SELECT * FROM sys.triggers
WHERE parent_id = 0
In order to list DML triggers you need to use parent_id <> 0
SELECT * FROM sys.triggers
WHERE parent_id <> 0
And if you want to list all triggers you can just ommit the WHERE clause
Wednesday, June 28, 2006
Murphy's Law And SQL Server
Why do things break the moment that the person who handles something is on vacation?
Here is the full story. A co-worker goes on vacation, he has his work on a specific server, this server is in our lab and not on the MCN (Mission Critical Network). First let me tell you that we are considered ‘renegades’ and that we don’t get operation support for some of our machines because we don’t use corporate standards and we need full control over our machines.
Anyway the server worked fine for 2 years, co-worker goes on vacation to Japan and the very next day I am getting crazy errors like can not use ad-hoc queries use linked server instead???? I click on the linked server and get OLEDB FoxPro driver missing errors? Fine let’s restart, simple enough right? Wrong! After hitting stop from Service Manager the status is ‘stopping’, after 20 minutes it is still ‘stopping’
Mmm I don’t want to to a hard reboot because I can corrupt all the databases and I don’t even know where half of the backups are (I don’t use this server). Okay let’s try NET STOP MSSQLSERVER. Nope application is in transition and can not be stopped error. Then it occurred to me that maybe we got hit by the slammer worm. Since it’s stopping I can not execute serverproperty so I right click on the sqlserver.exe file and look up the version number. It’s 2039, good that’s SP4. Then I hit restart from the start button and it restarted and everything was fine.
So what is Murphy's Law? Here is the Wikipedia link in case you want to know
http://en.wikipedia.org/wiki/Murphy's_law
Here is the full story. A co-worker goes on vacation, he has his work on a specific server, this server is in our lab and not on the MCN (Mission Critical Network). First let me tell you that we are considered ‘renegades’ and that we don’t get operation support for some of our machines because we don’t use corporate standards and we need full control over our machines.
Anyway the server worked fine for 2 years, co-worker goes on vacation to Japan and the very next day I am getting crazy errors like can not use ad-hoc queries use linked server instead???? I click on the linked server and get OLEDB FoxPro driver missing errors? Fine let’s restart, simple enough right? Wrong! After hitting stop from Service Manager the status is ‘stopping’, after 20 minutes it is still ‘stopping’
Mmm I don’t want to to a hard reboot because I can corrupt all the databases and I don’t even know where half of the backups are (I don’t use this server). Okay let’s try NET STOP MSSQLSERVER. Nope application is in transition and can not be stopped error. Then it occurred to me that maybe we got hit by the slammer worm. Since it’s stopping I can not execute serverproperty so I right click on the sqlserver.exe file and look up the version number. It’s 2039, good that’s SP4. Then I hit restart from the start button and it restarted and everything was fine.
So what is Murphy's Law? Here is the Wikipedia link in case you want to know
http://en.wikipedia.org/wiki/Murphy's_law
Subscribe to:
Posts (Atom)