Tech Talk A software developer on all things tech, and then some

21Nov/130

Exec sp_help on a table with a schema

Really quick post regarding sp_help and tables in Schemas (not in dbo). Usually when doing a sp_help, you can do something resembling the following :

EXEC sp_help TableName

However, if you try to do the same with a table with a Schema (SomeSchema.TableName) you will get an error "Incorrect syntax near '.'". To execute the sp_help on a table with a schema, simply surround the desired table and schema with single quotes.

EXEC sp_help 'SomeSchema.TableName'

That's all there is to it.

Tagged as: , No Comments
31Jan/130

Argument data type xml is invalid for argument 1 of like function

If you haven't realized it by now, you cannot do a LIKE without any magic on a XML column. If you do, you will get the following error :

Argument data type xml is invalid for argument 1 of like function

If you need to do a LIKE on a column with XML, there are a few ways to go about it. I however for the most part just need to do this through SQL Server Management Studio, so I use the quick solution of converting it to a varchar field, and problem solved. This would look like the following :

SELECT * FROM [tableName]
WHERE CAST([XMLColumnName] AS nvarchar(max)) LIKE N'%[textToSearchFor]%'

Hope this helps and happy coding!

Tagged as: , , No Comments
30Jan/130

What’s the Difference Between Where and Having in SQL?

A really quick post for you all today over the differences between Where and Having in SQL. This has been a question in a lot of interviews, but no one seems to know why and when to use each of these clauses.

Where

The where clause is used to filter based on criteria other than aggregates. So say for example you wanted to restrict the results to a single person, "Jacob Saylor", you would do something similar to the following :

SELECT FullName, SUM(Deposit) as Balance
FROM Accounts
WHERE FullName = 'Jacob Saylor'
GROUP BY FullName

A really straight forward example showing the most common use of both the where and the group by.

Having

The having clause is used to filter based on aggregate functions in conjunction with a group by clause. It can also be used with just a group by clause as well (See the Following example).

SELECT FullName, SUM(Deposit) as Balance
FROM Accounts
GROUP BY FullName
HAVING FullName = 'Jacob Saylor'

This is used a lot less often than the where due to a performance hit. See Performance below.

Where and Having Working Together

So now that we have seen Where and Having used in their own context, let's use them together. In the following example we use where to restrict all deposits greater than $5, and where the sum of these deposits are greater than $25.

SELECT FullName,SUM(Deposit) AS DepositsGT5
FROM Accounts
WHERE Deposit > 5
GROUP BY FullName
HAVING SUM(Deposit) > 25

This will be the far best way to use them together, filtering content with the Where and aggregates with the Having.

Performance

By SQL Standard, a Where clause will restrict the set before returning the result set, while the Having clause will restrict the results after it is returned. This means when you don't have to use a having clause, don't.

Tagged as: , , No Comments
14Nov/120

SqlDataAdapter Will Not Always Return a Result, SSMS Will

The Issue

I ran into a strange issue with an ASP.NET site (MVC2 - 4.0 Framework) where when executing a stored proc, SqlDataAdapter.Fill(DataTable) will not return any rows some of the time, but SQL Server Management Studio will almost immediately return results. The full method calling the stored procedure is below : (Auto-generated from LLBL Pro V2.6)

public virtual bool CallRetrievalStoredProcedure(string storedProcedureToCall, SqlParameter[] parameters, DataTable tableToFill)
{
	SqlCommand command = new SqlCommand(CreateCorrectStoredProcedureName(storedProcedureToCall));
	command.Connection = (SqlConnection)base.GetActiveConnection();
	if(base.IsTransactionInProgress)
	{
		command.Transaction = (SqlTransaction)base.PhysicalTransaction;
	}
	command.CommandType = CommandType.StoredProcedure;
	command.CommandTimeout = base.CommandTimeOut;

	using(SqlDataAdapter adapter = (SqlDataAdapter)CreateNewPhysicalDataAdapter())
	{
		adapter.SelectCommand = command;

		for(int i=0;i<parameters.Length;i++)
		{
			command.Parameters.Add(parameters[i]);
		}
		adapter.Fill(tableToFill);
	}
	command.Dispose();
	return true;
}

This issue occurs on both our Development and Testing servers. The difference however is that on the development server, it will usually fail once or twice then work, but on the testing server it will return zero rows every time.

23Oct/120

View The Last 50 Queries Ran on SQL Server

I ran into an issue where I needed to see the last few queries ran on SQL Server (Currently using SQL Server 2005). There were quite a few suggestions out there, but one I found on Stack Overflow seemed to be formated the best and didn't require me messing with the query. (See Query Below)

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
  execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC

This however wasn't working for me on the database I was in. I checked my compatibility level (code below), due to the suggestions in the comments, and it returned 80, not the expected 90.

SELECT compatibility_level from sys.databases WHERE name=db_name()

I then switched my db to tempdb, ran the query, and it worked. It really doesn't matter where you run the query, it will pull the last 50 ran on any database. Just make sure the db you run it on has a compatibility level > 80 so you can use the cross apply (tempdb is usually untouched, so it's a good one to default to).

Notes : I would suggest using the SQL Profiler if you can, however this is a great quick solution when you don't have time to wait for permissions to change, etc...

11Jan/120

Nested Case Statements in SQL

Need to do nested Case statements in SQL? Use the following syntax. This should result in 'a1b1 - yar'

DECLARE @a INT
DECLARE @b INT

SET @a = 1
SET @b = 2

SELECT 
CASE
	WHEN @a = 1 THEN 
	CASE 
		WHEN @b = 1 THEN 'a1b1'
		WHEN @b = 2 THEN 'a1b2 - yar'
	END --Ends the inner case
	WHEN @a = 2 THEN 
	CASE
		WHEN @b = 1 THEN 'a2b1'
		WHEN @b = 2 THEN 'a2b2'
	END --Ends the inner case
END --Ends the outer case

This took me a little time to work out the syntax, but pretty simple once I understood case statements a little better.

For some more info on case statements, you can go to the msdn  article located at http://msdn.microsoft.com/en-us/library/ms181765.aspx

2Dec/110

Automated Backups of Your SQL Database

Ever find yourself taking precious time and backing up all your databases, only to forget and be behind? I am currently working on some Alpha-PreAlpha software whose database structure changes with time like any other application. To solve the issue with remembering to back up however, I decided to automate it, and not have to worry. There probably is a lot better ways to automate it, but this is the process that came to mind first since I have worked with batch files and automation before.

29Aug/110

How to Add, Modify, Rename, and Delete a Column in SQL

There has been a few times where I needed to compose a script to update a customer's database, and I've had to use probably the most popular of the SQL ALTER TABLE syntax: Add, Modify, Rename, and Drop. To help save for my own notes and to possibly give some help,  I thought I'd write a quick article on these 4 things.

16May/110

Specify Your Own Select And Edit Top(n) in SQL Server Management Studio

When right clicking a table in SQL Server Management Studio, you have an array of options. Probably the two that I use most however is the "Select Top 1000 Rows" and the "Edit Top 200 Rows". However, sometimes you want to look at a little more than that. I know most of the time I want to select all from a table, not just the top 1000. Well in SQL Server 2008 (might work in older versions as well) you can change the default select and edit numbers.

1) Go to Tools -> Options
2) Select SQL Server Object Explorer
3) You will be prompted with a screen which looks similar to the one below. Edit the "Value for Edit Top <n> Rows command" and the "Value for Select Top <n> Rows" to the desired value. ( 0 will select all)

23Mar/110

Fixing A Blue Filled Checkbox in .Net

If you ever seen the above check mark, you are probably wondering what the heck is going on. First off it's not checked or checked, some in between state where it's just filled with a blue square. Second off you even, most likely, have a variable bound to it, and yet it still displays as this!

The issue is that the variable that you have it bound to is null. This is the default way to show null in a check box, not just an empty square like you would expect such as when the value is equal to false. There is a solution you can try. If you are binding to a database and using SQL, you can use the following code to set all the values to null, then add a default of zero to the column to prevent the blue box from showing up.


UPDATE tableName
SET columnName= 0
WHERE columnName= null

ALTER TABLE tableName
ADD DEFAULT 0 FOR columnName;

If you are not using SQL, then check in visual studio for what ever data object you are binding to, and set it's default value to zero. (I'm not going to give a tutorial on each way since there are a few).

Hope this helps, and happy coding!

15Mar/110

Drop, Truncate, and Delete : The Difference


If you wanted to get rid of all the data in a table, then you could you either of the three. However there are certain situations which using a different one of these is the best route to go.

2Feb/110

Quick Newbie SQL Tips

I go back and forth with different versions of SQL which at times will require different syntax. I started using primarily SQL Server 2008 R2 so I needed an update on some certain functions. To help me remember and to save you some time, here they are.

Adding Multiple Colums

There has been a few times where I needed to add multiple columns to a table to update a customer. Here is the old way

ALTER TABLE tableName
ADD( column1Name type,
        column2Name type,
        column3Name type)
GO

Now you dont have to worry about the parenthesis when adding multiple columns to the table :

ALTER TABLE tableName
ADD column1Name type,
      column2Name type,
      column3Name type
GO

Adding Foreign Keys Manually

Sometimes you dont want to sit there and fill out a database diagram, or you cant. Well here is a manual solution to add a foreign key constraint on a table manually.

alter table tableName
add constraint tableName_ColumnFK_FK FOREIGN KEY (ColumnFK) REFERENCES otherTable(columnPK)

Where tablename is the name of the table you are adding the foreign key to, ColumnFK is the name of the foreign key column which resides in the table you are adding the foreign key to, otherTable is the name of the table which has the primary key that you are referencing with your foreign key, and columnPK is the name of the column which is the primary key on the table you are referencing. tableName_ColumnFK_FK is the name of the constraint, but I would strongly recommend keeping the naming convention to keep track of all the foreign keys.

Using The Bit Datatype

Using the bit datatype will decrease the space needed for this simple data. When all you need is a simple yes or no, then why not just store it as a bit, rather then at the worst case 32 bits (signed int) ?

Setting The Length

Setting the length will overtime greatly reduce the size of your database. This in conjunction with varchar will help you ten fold. If you create the following :

column1 text

or

column1 char(MAX)

then you are creating a space hog. Make the size either vary with what is in the field, or something close to what is needed. For example, if you need a phone number, set the field to varchar(14) instead of the max.

Now the other question when dealing with varchar(), =Should I use varchar() or nvarchar()?=. The easy way to explain this: Varchar() is used for the variable length for english characters, etc... while nvarchar is used to store more characters, such as the Chinese fonts. Space is not typically an issue, but it does call for double the amount of reads which in turn slow down performance.

%d bloggers like this: