There are so many opportunities for many of you to learn more about SQL Server. It's amazing to think how many more choices we have today than I did early in my career. When I moved to Denver, we had a user group that met somewhat sporadically, an MSDN lunch hour every month, the PASS Summit, and training classes from various vendors like New Horizons. That was about it, with only a few online resources. SQLServerCentral didn't even exist then.
That's changed today and when I look at my schedule, it's incredible to think how many of them I'm going to be a part of this fall. I've got a busy travel schedule the rest of the year, and I'll only touch a small portion of the events that you could use to increase your SQL Server knowledge.
There are quite a few conferences this year. DevConnections in a few weeks, the PASS Summit mid month, and SQL Intersection at the end of October. I'll be speaking at the former and latter, skipping the middle one this year. There are two SQL Rallys, Nordic and Amsterdam in Europe. There are also lots of pre- and post-cons and other workshops sessions around the events. There are even amazing training classes from vendors like SQLskills and Brent Ozar Unlimited that will teach you a tremendous amount about SQL Server. I'm hoping to attend one of these sessions in the next year.
If your boss won't pay for a conference or class, there are plenty of free events as well. SQL Saturdays are everywhere, almost every weekend. I'll be at San Diego, Denver, Charleston, Portland, and Washington DC this year, but there are plenty more. Many of them offer pre-cons, at low costs. You might consider covering the cost yourself and asking your boss to pay for the time off.
My employer, Red Gate Software, is putting on some of their own events. We are doing seminars before some of the SQL Saturdays and I have SQL in the City events in Pasadena, Atlanta, and Charlotte this year. These are free events, just register and come.
There are all levels of costs, online, local, worldwide, etc. resources that you can use to learn more about SQL Server. I'd highly encourage you to make a plan and improve your SQL Server skills over the next year. I guarentee there's something for every budget and situation.
MidnightDBA and Red Gate have joined together to produce a new book on SQL Server, written by 15 first-time authors. The resulting book, TribalSQL, includes everything you should know about SQL Server that isn’t taught in traditional training, they call it Tribal knowledge. More »
A Dominoes game of Texas 42 inspires Joe Celko to explore unusual uses for check constraints and views. Sometimes, the best way of discovering useful SQL techniques is to tackle the more unusual problems. More »
The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Get your copy from Amazon today.
Yesterday's Question of the Day
(by bitbucket-25253 ):
I have the following settings for my SQL Server 2012 2008R2
SET NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,ANSI_NULLS ON;
I execute the following T-SQL:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SomeTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[SomeTable]
CREATE TABLE DBO.SomeTable( [Case] char(3),
[Hours] int, [Type] int)
select 'xxx', 3, 1 union all
select 'xxx', 3, 2 union all
select 'xxx', 4, 1 union all
select 'xxx', 1, 1
I then execute the following T-SQL statement:
when [Type] = 2 then -1 * [Hours]
end) as TotalHours
The Questions are:
Does the create table statement fail or succeed?
If the create table statement succeeds what value of TotalHours is returned by the Select statement?
If the create table statement fails what error msg number is returned when the Select statement is executed?
5 hours is returned by the SELECT statement
The create table statement succeeds
Explanation: In Microsoft SQL Server delimited identifier is a sequence of characters enclosed in double quotation marks (") as the default. When quoted_identifier option is set off, only brackets () are used to delimit identifiers and double quotation marks can be used to delimit character strings. It is allowed to use SQL reserved words as delimited identifiers. The length of the delimited identifier cannot exceed 128 characters not counting the delimiter characters. The body of the identifier can contain any combination of characters in the current code page except for the delimiting characters themselves.
Over the years (and various versions of SQL Server), I have always had a need for different bits of information from the system tables to view activity on a SQL instance. The Microsoft "sp_who" procedures have always provided good information, but never really everything that I needed (and, more importantly, how I needed it... hence the reason for building my own version).
It can be run as is (example: EXECUTE dbo.usp_who5) or with optional input filter parameters:
@Filter: Limit result set by passing one or more values listed below (can be used individually or combined in any manner):
A - Active SPIDs Only
B - Blocked SPIDs Only
C - Exclude "SQL_Statement_Batch", "SQL_Statement_Current", and "Query_Plan_XML" fields from output (less resource-intensive on busy servers)
X - Exclude System Reserved SPIDs (1-50)
@SPID: Limit result set to a specific SPID
@Login: Limit result set to a specific Windows user name (if populated), otherwise by SQL Server login name
@Database: Limit result set to a specific database
@SQL_Text : Limit result set to SQL statement(s) containing specific text (ignored when "@Filter" parameter contains "C")
When using the procedure you will notice that query output contains a lot of handy information:
SPECID: System Process ID with Execution Context ID
Blocked: Blocking indicator (includes type of block and blocking SPID)
Running: Indicates if SPID is currently executing (X), waiting (*), inactive (blank), has open transactions (•), or is a background task (--)
Login_ID: Displays Windows user name (or login name if user name is unavailable)
Login_Name: Full name of the user associated to the Login_ID (if available)
Elapsed_Time: Total elapsed time since the request began (DAYS HH:MM:SS)
CPU_Total: Cumulative CPU time since login (DAYS HH:MM:SS)
CPU_Current: Cumulative CPU time for the current process (DAYS HH:MM:SS)
Logical_Reads: Number of logical reads performed by the current process
Physical_Reads: Number of physical reads performed by the current process
Writes: Number of writes performed by the current process
Pages_Used: Number of pages in the procedure cache currently allocated to the process
Nesting_Level: Nesting level of the statement currently executing
Open_Trans: Number of open transactions for the process
Wait_Time: Current wait time (DAYS HH:MM:SS)
Wait_Type: Current wait type
Last_Wait_Type: Previous wait type
Status: Status of the current process
Command: Command currently being executed
SQL_Statement_Batch: Batch SQL statement of the associated SPID
SQL_Statement_Current: Current SQL statement of the associated SPID
End_Of_Batch: Indicates if the current SQL statement is the last of the entire batch
Query_Plan_XML: Execution plan of the associated SPID (in XML format)
Plan_Cache_Object_Type: Displays which mechanism is being used for the cached plan (used in conjunction with Plan_Object_Type)
Plan_Object_Type: Displays which mechanism is being used for the cached plan (used in conjunction with Plan_Cache_Object_Type)
Plan_Times_Used: Number of times the plan has been utilized since its creation
Plan_Size_MB: Size consumed by the plan in megabytes (MB)
Since_SPID_Login: Total elapsed time since the client logged in (DAYS HH:MM:SS)
Since_Last_Batch: Total elapsed time since the client last completed a remote stored procedure call or an EXECUTE statement (DAYS HH:MM:SS)
Workstation_Name: Workstation name
Database_Name: Database context of the SPID
Application_Description: Application accessing SQL Server
SPECID: System Process ID with Execution Context ID
If you ever need to remember what the input parameters / output columns are and what they mean, you can simply execute the following:
EXECUTE dbo.usp_who5 '?'
Best of all, if there is any blocking occurring on the server it will come right to the top of the result set and show you the details immediately (which SPID is blocked and by who, which SPIDs are blocking other processes, which are running in parallelism).
I typically map the procedure to keyboard combinations in SQL Server in order to run it on the fly with various input parameter combinations.
multi-valued report parameter weirdness
- SSRS 2012, SQL 2012
I watched Brian Knight's video on filtering a report using a multi-valued parameter (here:[url]http://www.sqlservercentral.com/articles/Video/64369/[/url]), and after seemingly...
Help On Query
CREATE TABLE [dbo].[tmp]([msisdn] varchar(20) )
DECLARE @intFlag bigINT
SET @intFlag = 15210000000
WHILE (@intFlag <=15219999999)
insert into tmp select @intFlag
set @intFlag= @intFlag + 1...
Adding time intervals in minutes and display as csv
- i have a table with three columns as
col1 col2 col3
11:30 13:30 15
00:10 01:40 5
the out put should be as follows
- I have string of format with two delimiters ‘|’ pipe and ‘,’ comma
And have to insert into table columns as below
Problem in query
i have a problem regarding query,
my data is like this
123-----7/1/2013 08:00:00 PM-----I---------
123-----7/2/2013 02:00:00 AM-----O---------
I means in and O means...
- Hi Everyone,
I am having a problem while using Lookup
Scenario : i am loading Dimension table and Fact Table in...
PK vioaltion in replication
- I have no work experience in replication , how to solve the PK vioaltion in transactional replication instead of ignoring the...
Sql Server Authentication
- Please help me, what are the system procedures and system functions that are used for validating whether a user is...
finding value in a string
- I have a text value in a string which I can convert to varchar(1000). field I am going to convert...
How do I remove the Nulls in my output?
- CREATE TABLE #Total_Count
(NUM_DEATHS_1870 numeric (8,0), NUM_DEATHS_1880 numeric(8,0))
INSERT INTO #Total_Count (NUM_DEATHS_1870)
(SELECT COUNT (*) COD
WHERE YR_Died = '1870')
INSERT INTO #Total_Count...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.