Showing posts with label Tips and Tricks. Show all posts
Showing posts with label Tips and Tricks. Show all posts

Thursday, July 13, 2017

SSMS: When did the query finish, when did the query start?



This is a quick post but it might be an item you can add to your Today I Learned list  :-)

Let's say you get to a SSMS query window and you see the following



You can tell it ran really fast and you can tell that 26080 rows were affected.  Can you tell when it started and when it finished?

Yes, you can!!!

Hit the F4 button or select View-->Properties Window from the menu bar

Here is what I see



As you can see there are a bunch of properties about the query

For example

Start time: 7/13/2017 9:58:01 AM
Finish time: 7/13/2017 9:58:02 AM
Elapsed time: 00:00:00.766


There you have it, a quick and easy way to tell when something ran in your query windows

Wednesday, August 13, 2008

SQL Tip, Compiling Your SQL Without Running It to See If It Would Run

Let's say you have a big SQL script with a ton of code and you want to make sure it runs but you don't want to execute it because it updates tables, deletes data etc etc.
Take this simple example

SELECT GETDATE()
GO
SELECT 1/asasasas
GO


You can probably guess that the second statement is not valid, when you have a lot of code it is more difficult to spot these things.
Execeute the code above and you will get this


(1 row(s) affected)

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.

SQL server has the SET NOEXEC statement. From BOL:
When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of
Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all
batches are executed after compilation.

The execution of statements in
SQL Server consists of two phases: compilation and execution. This setting is
useful for having SQL Server validate the syntax and object names in
Transact-SQL code when executing. It is also useful for debugging statements
that would usually be part of a larger batch of statements.

The setting
of SET NOEXEC is set at execute or run time and not at parse time.


So execute the code below

SET NOEXEC ON
GO

SELECT GETDATE()
GO
SELECT 1/asasasas
GO
SET NOEXEC OFF
GO



As you can see the output is the following:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.


You never see the getdate. Parsing that code will not throw an error and because of deferred name resolution you can fat-finger table names and it will parse without a problem.

Wednesday, July 02, 2008

Awesome Collection Of ASP.NET Hacks, Tips and Tricks

We added a bunch of ASP.NET Hacks, Tips and Tricks

we have the following categories
1 Applications
2 Caching
3 Controls
4 Database
5 Dates
6 Debugging
7 Email
8 Encryption
9 Files
10 Images
11 Javascript
12 Objects and Classes
13 Pages
14 Sessions
15 Strings
16 Validation
17 Visual Studio
18 Web

The URL to these hacks is here: http://wiki.lessthandot.com/index.php/ASP.NET_Hacks
Bookmark that URL because we will be adding more hacks, tips and tricks

Thursday, June 26, 2008

Working On SQL Admin Hacks, Tips and Tricks

I haven't posted for a while because I have been working on SQL Admin Hacks, Tips and Tricks lately. it is still a work in progress but below is what is on the wiki currently. It is not yet categorized but we will do that once we get more of these hacks done. To see what it will look like when it is done take a look at the SQL Server Programming Hacks

Can you think of any admin stuff you would like to see? This is what we have right now

Find Primary Keys and Columns Used in SQL Server
Get The Domain Name Of Your SQL Server Machine With T-SQL
Grant Execute/SELECT Permissions For All User Defined Functions To A User
Grant Execute Permissions For All Stored Procedures To A User
Kill All Active Connections To A Database
SQL Server 2008: When Was The Server Last Started?
Check If Auto Update Statistics Is Enabled By Using DATABASEPROPERTY
Three Way To List All Databases On Your Server
Generate A List Of Object Types By Using OBJECTPROPERTY
How to find all the tables and views in a database
Find Out Server Roles For a SQL Server Login
Which Service Pack Is Installed On My SQL Server
Test SQL Server Login Permissions With SETUSER
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Compare Tables With Tablediff
Find All Tables Without Triggers In SQL Server
Find All Tables With Triggers In SQL Server
Create Stored Procedures That Run At SQL Server Startup
Cycle The SQL Server Error Log
How to read sql server error messages
Use OBJECT_DEFINITION To Track SQL Server Stored Procedure Changes
SQL Compare Without The Price Tag
How To Get The Database Name For The Current User Process
How To Find Out Which Columns Have Defaults And What Those Default Values Are
Fixing Cannot add, update, or delete a job that originated from an MSX Server Error after renaming a server

Thursday, May 08, 2008

How to log when a function is called?

This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb


--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go

--Here is the proc
CREATE PROC prLog
@SomeValue
varchar(50)
AS
INSERT
LogMeNow (SomeValue) VALUES(@SomeValue)
go

--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE
@SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

Now call the function a couple of times

SELECT
dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)



And look inside the table




SELECT * FROM LogMeNow

What if you were to run this?


SELECT dbo.fnBla(4),* FROM sys.sysobjects


See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!


I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

Tuesday, May 06, 2008

Do you depend on sp_depends (no pun intended)

HTML Source EditorWord wrap I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this

First create this proc

CREATE
PROC SomeTestProc
AS
SELECT
dbo.somefuction(1)
GO


now create this function

CREATE
FUNCTION somefuction(@id int)
RETURNS int
AS
BEGIN
SELECT
@id = 1
RETURN @id
END
Go


now run this


sp_depends
'somefuction'

result: Object does not reference any object, and no objects reference it.



Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct



SQL Server 2005 makes it pretty easy to do it yourself



SELECT

specific_name,*

FROM information_schema.routines

WHERE

object_definition(object_id(specific_name)) LIKE '%somefuction%'

AND

routine_type = 'procedure'

BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character




Friday, March 28, 2008

How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

A variation of this question popped up twice in the SQL programming newsgroup since yesterday, this means it is time for a blogpost.
Let's say you have a proc like this




CREATE PROC prUpdateTable
@Salesman
int = -1
AS

..........

If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do?
You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this




UPDATE table
SET
Column = COALESCE(NULLIF(@variable,-1),Column)



Here is some code to demonstrate that




CREATE TABLE #foo (id int,salesman int)
INSERT #foo VALUES(1,1)
INSERT #foo VALUES(2,1)
INSERT #foo VALUES(3,1)
INSERT #foo VALUES(4,1)



SELECT * FROM #foo
-------------
1 1
2 1
3 1
4 1

DECLARE @salesman int
SELECT
@salesman = 5

--Column value will change to 5
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =1

--Column value won't change
SELECT @salesman = -1
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =2

--Column value won't change
SELECT @salesman = NULL
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =3

--Column value will change to 3
SELECT @salesman = 3
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =4

--And here is the output, as you can see when @salesman was -1 or NULL the table did not get updated
SELECT * FROM #foo
-------------
1 5
2 1
3 1
4 3

DROP TABLE #foo





As you can see only the first and the last update statement changed the value of the salesman column
Of course you would never do this if you were to update only one column, you would skip the update instead. If you have to update multiple columns then this is something you can use instead of writing a bunch of dynamic SQL or nested IF statements.




Friday, January 25, 2008

Tip: Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both

This question is asked every now and then so I decided to do a little blog post. How can you quickly without writing a bunch of OR statements determince if any columns have a NULL value, a value of 0 or if the value is 0 or NULL.
To test for NULL is very easy, you just concatenate the columns since NULL + anything else is always NULL. Okay that also depends on a setting.

Run this

SET
CONCAT_NULL_YIELDS_NULL ON
SELECT
NULL + '1' --NULL

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
NULL + '1' --1

As you can see if CONCAT_NULL_YIELDS_NULL is OFF then the result is 1

Now take a look at this


SET CONCAT_NULL_YIELDS_NULL ON
SELECT
NULL + 1 --NULL



SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
NULL + 1 --NULL


So with numeric values it behaves differently. Either way by default CONCAT_NULL_YIELDS_NULL is set to on
To test for NULLS or zeroes you use NULLIF
To test for zeros you can combine COALESCE and NULLIF

Here is the code which shows all of that

CREATE
TABLE #test(column1 int,column2 varchar(4),column3 float)

INSERT
#test VALUES(2,'2',2)
INSERT #test VALUES(0,'1',0)
INSERT #test VALUES(null,'1',0)
INSERT #test VALUES(1,null,0)
INSERT #test VALUES(0,'1',null)
INSERT #test VALUES(null,null,null)



--Any column is Null
SELECT * FROM #test
WHERE column1 + column2+column3 is null

Output
------------
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL



--Any column is Null or zero
SELECT * FROM #test
WHERE NULLIF(column1,0) + NULLIF(column2,0)+NULLIF(column3,0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL




--Any column is zero
SELECT * FROM #test
WHERE NULLIF(COALESCE(column1,1),0) +
NULLIF(COALESCE(column2,1),0)+
NULLIF(COALESCE(column3,1),0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL


DROP TABLE #test

Wednesday, January 02, 2008

Use the *1 trick to do math with two varchars, this prevents the Invalid operator for data type. Operator equals subtract,type equals varchar message

Someone had code like this on the tek-tips forum

DECLARE @v varchar(24)
SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) -SUBSTRING(@v,10,4)

If you run this code, you will get the following message
Server: Msg 403, Level 16, State 1, Line 4
Invalid operator for data type. Operator equals subtract, type equals varchar.

Instead of casting to integers you can also use this little trick. You basically multiply one of the values by 1

DECLARE @v varchar(24)
SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) *1 -SUBSTRING(@v,10,4)



Another example. This doesn't work
SELECT '2' - '1'

This does work
SELECT '2' * 1 - '1'