[RESOLVED] Most efficient way to determine if a column exists in a T-SQL table

[cory_jackson]

This is a tangent from my last post.

I have a potentially large table on a SQL Server and I do not want to maintain a local copy in a DataTable. I simply need to know if one column value exists. The ultimate goal is to add a row if it doesn't but I want to focus on the condition first.

Right now I'm using SELECT COUNT(ColumnName) FROM TableName WHERE ColumnName = 'SomeValue'. If it's zero I know it doesn't exist. But is there a better SQL command to make this determination?

Suppose this SQL command or one like it is the one I end up using. Is there a way to essentially do this command using LINQ2SQL or Entity Framework without having to maintain a local DataTable?

[jmcilhinney]

Right now I'm using SELECT COUNT(ColumnName) FROM TableName WHERE ColumnName = 'SomeValue'. If it's zero I know it doesn't exist. But is there a better SQL command to make this determination?

The only other option I can think of would to use EXISTS, but I've never tried to use that as a query output. I assume that it should just return a BIT value though, so that would be a Boolean in VB.

Suppose this SQL command or one like it is the one I end up using. Is there a way to essentially do this command using LINQ2SQL or Entity Framework without having to maintain a local DataTable?

If you're using an ORM then I think you'd have to query to retrieve that matching record. Then you'd either get an existing entity that you could update and save or you'd get nothing so you'd create a new entity and save it.

[szlamany]

Select Count() is work for SQL

Exists() is just a check - less work.

Show the SQL you would use to add the row....

If it's what I think then this single SQL statement would do the INSERT and EXISTS at once - right?

Insert into SomeTable Select Col1, Col2, Col3 Where Not Exists(Select * From ...)

The SELECT * is fine in this instance as it's not returning columns - it's just a syntax placeholder.

[cory_jackson]

Thank you both.

EXISTS sounded very interesting but it appears that EXISTS is only used in combination with sub-queries. It returns data and not a Boolean as John and I thought it might. So this is a isn't an option. If I have this wrong please let me know.

As I have considered my logic further I've decided that if there is no query that has a Boolean result it would be better if I try to return a row or column value from that row if it does exist. Many times that's the next operation if the result is true.

SELECT TOP 1 Tools.ID
FROM Tools
WHERE Tools.SerNum = '1234'

For my current challenge this it the best I've managed so far as the best solution. This returns a small bit of string and that string is all I need. I'm not even updating or changing anything. I can do this old school but is there a way to do this with an ORM like LINQ2SQL or EF?

Using ADO I think what I would do is use a DataView with just the ID and SerNum columns. Then do a Fill with the criteria on the SerNum. Then simply the ID from row zero. Also the Fill should remove the previous entries. Does that make sense? I'm trying to decide if I should look into EF to see if there's a way to do the same.

[szlamany]

Thank you both.

EXISTS sounded very interesting but it appears that EXISTS is only used in combination with sub-queries. It returns data and not a Boolean as John and I thought it might. So this is a isn't an option. If I have this wrong please let me know.

Yes - you are wrong. In the example I posted it's in a WHERE clause so it's not returning data.

You can do this:

Select Case When Exists(Select...) Then 'Y' Else 'N' End

Then use EXECUTESCALAR to return the Y/N value. That will return the Y/N value into a string variable without the overhead of a recordset and rows and columns.

That will be the fastest way to determine if a ROW exists (you title mentioned column - I'm guessing now that was misspoken).

Now - from your last post about LINQ2SQL - I'm feeling you are just thinking about all this in respect to what you want to accomplish, as opposed to coding and having a specific issue/road block you need help with.

[JuggaloBrotha]

Personally I would query the system table(s) to see if the column exists in the schema rather than querying the table and getting a sql error because it doesn't exist:

IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'columnName' AND Object_ID = Object_ID(N'tableName'))
BEGIN
  SELECT 'Y' As [ColumnExists];
  RETURN 1;
END
ELSE
BEGIN
  SELECT 'N' As [ColumnExists];
  RETURN 0;
END

You can make that a stored proc in your database, then replace N'columnName' with @ColumnName and N'tableName' with @TableName, then just call the stored proc in your app.

[szlamany]

That will be the fastest way to determine if a ROW exists (you title mentioned column - I'm guessing now that was misspoken).

I believe the question here is about a ROW existing with a column value.

[cory_jackson]

Forgive me confusing things. I have muddied the water with my confusing terms. I started doing Access where there were 'records' and 'fields' which is conceptually more sensible to me. IE that a record contains fields whereas the row and column concept is more like a coordinate system. IOW in my mind the table had columns and a row does not. Can't have a column of one value to my old way of thinking. When I visualize a column I see multiple values aligned vertically, not one. Szlamany is completely right. Sorry about that.

Szlamany that's a perfect little solution you have and I'm going with that.

JuggaloBrotha thanks for your solution as well.

I have one more unimportant question. I'm just curious. I created a simple test application for this but when I tried to cast it to a Boolean it failed. I changed the query to be "true" and "false" and that worked. So am I to understand it's returning a text datatype?

[szlamany]

I've never used BOOLEAN data type in MS SQL. I've seen people with ACCESS-backgrounds make use of it.

I always go with a Y/N field since most UI elements work well with that.

[cory_jackson]

Understood. So what do you do for other types like dates, double, or binary?

I've been playing with SQL parameters and I understand them a lot better now. I've used them for some time with only half understanding them. But something I don't understand is if a SQL command is text how does one add or receive non-text data to and from the server. Is there an simple explanation for this?

Thanks again for your responses.

[jmcilhinney]

I've never used BOOLEAN data type in MS SQL. I've seen people with ACCESS-backgrounds make use of it.

I always go with a Y/N field since most UI elements work well with that.

If you use the `bit` data type in SQL Server then you would have to use 1 and 0 literals in SQL code but any ADO.NET code will use the .NET Boolean structure, so it's easiest to work with that in VB code. Instead of this:

Select Case When Exists(Select...) Then 'Y' Else 'N' End

you could do this:

Select Case When Exists(Select...) Then CAST(1 as bit) Else CAST(0 as bit) End

If you were to then call ExecuteScalar you would get True or False returned. Without the CAST calls, you'd get an Integer back in VB, which you could pass to Convert.ToBoolean to get True or False.

[szlamany]

If going that route even better with

Select CAST(Case When Exists(Select...) Then 1 Else 0 End as bit)

imo, anyway

[cory_jackson]

That would return a Boolean?

[szlamany]

Understood. So what do you do for other types like dates, double, or binary?

I've been playing with SQL parameters and I understand them a lot better now. I've used them for some time with only half understanding them. But something I don't understand is if a SQL command is text how does one add or receive non-text data to and from the server. Is there an simple explanation for this?

Thanks again for your responses.

I've always driven myself towards the least number of data types possible.

I would never use a double - the precision problems kill that data type. If it has a decimal it goes into MONEY data type. I always CONVERT() or CAST() my money fields to varchar(100) on SELECT's facing the client.

For integer types I do not need the small ones - only need to support one of those.

Dates go into datetime data types. Time goes into VARCHAR(4) - I've got a lot of support code that understands time represented in that fashion (I know TG hates time in varchar() fields).

Whenever I bring a date out to the client side I always CONVERT(varchar(10),DateField,101) to get a MM/DD/YYYY that has leading zeroes (01/01/2015, 02/01/2015...).

KISS...

By keeping my life simple I can do stuff like this in a web method and feel confident I've got all angles nailed.

    Private Sub SetParameters(ByRef cmd As SqlCommand, ByVal blnDoFromWho As Boolean, ByVal fromwho As String _
                              , ByRef objReturn1 As Dictionary(Of String, String) _
                              , Optional ByRef objReturn2 As Dictionary(Of String, String) = Nothing _
                              , Optional ByVal parentfromddtype As String = "" _
                              , Optional ByVal parentfromwho As String = "" _
                              , Optional ByVal username As String = "" _
                              , Optional ByVal addkey As String = "" _
                              , Optional ByVal popupkey As String = "" _
                              , Optional ByVal choice As Integer = 0)
        For x As Integer = 0 To cmd.Parameters.Count - 1
.
.
.
                Dim strValue As String = ""
                Dim blnFromWho As Boolean = False
.
.
.
                If strValue = "" And (cmd.Parameters(x).SqlDbType = SqlDbType.DateTime _
                                    Or cmd.Parameters(x).SqlDbType = SqlDbType.Int _
                                    Or cmd.Parameters(x).SqlDbType = SqlDbType.Money) Then
                    cmd.Parameters(x).Value = DBNull.Value
                Else
                    cmd.Parameters(x).Value = strValue
                End If
            End If
        Next
    End Sub

[szlamany]

That would return a Boolean?

If you are in MS SQL your user will want to open a table in Excel, for instance. Just yourself being able to easily eyeball tables in SSMS...

Y/N is easy on the eyes for a lot more users then some 0/1 column.

I consider these aspects important. I do not need a dozen data types all with their own intricacies.

Just an opinion...

[cory_jackson]

Thank you very much but I am still a bit perplexed. You explanation of dates makes it sound as if you're sending string representations of dates, not actual DateTime data type. IOW do all data types have to be converted to string to send to or be received from a SQL Server?

[jmcilhinney]

Thank you very much but I am still a bit perplexed. You explanation of dates makes it sound as if you're sending string representations of dates, not actual DateTime data type. IOW do all data types have to be converted to string to send to or be received from a SQL Server?

Apparently, human readability is a significant driver for the work szlamany does. If my impression is correct, he is a database developer primarily. As an application developer, you will generally need to think more about how your code interacts with the data than what it will look like to a human being. If you're displaying data in your app then Booleans generally become check boxes anyway, so text containing "Y" or "N" is of no benefit. If you do need to export data to a format like Excel of PDF then you would need to consider what would be most readable for the user.

[szlamany]

Last time I dealt with dates as datetime datatype on the .Net client side you had to jump through hoops to get the datetime picker to handle the null for blank date.

Seems that regardless of where you shift the effort, the effort is always going to be there.

Once you get into web apps it's all strings anyway.

So where you land is all up to you but the trip is exactly the same regardless.

[cory_jackson]

Thanks John. You're correct. But I'm still trying to understand what data type is flying back and forth on the network between the server and me. The sever returns my date of birth. If I sniffed the packets will I find string of "8/19/1968" or the binary representation of the integer 25069?

[jmcilhinney]

Last time I dealt with dates as datetime datatype on the .Net client side you had to jump through hoops to get the datetime picker to handle the null for blank date.

Seems that regardless of where you shift the effort, the effort is always going to be there.

That's a deficiency in the WinForms DateTimePicker control specifically. It's not necessarily the case with other technologies and there are alternatives to the standard DateTimePicker that can handle NULL values. There's also the case where a column can't contain NULL, so that issue goes away.

Once you get into web apps it's all strings anyway.

So where you land is all up to you but the trip is exactly the same regardless.

It's all strings at the browser but that doesn't mean that it should be under the hood. For instance, if you're using ASP.NET MVC then your model can have a DateTime property and the model binder will do the work of validating user input. If you have a String property then you have to validate it yourself. You also have to convert the text to a DateTime in order to sort or compare in any other way. I have certainly encountered situations where text was the best option, which are generally read-only, but I would normally use the .NET type that was intended to store data of the type I have.

[jmcilhinney]

Thanks John. You're correct. But I'm still trying to understand what data type is flying back and forth on the network between the server and me. The sever returns my date of birth. If I sniffed the packets will I find string of "8/19/1968" or the binary representation of the integer 25069?

All data in binary when you get down to it. If you had a binary date in the database and a binary DateTime in the application. It would be silly to convert that binary value to text, which means binary values for each of the characters in that text, in between because it would mean extra processing at each end and also more data to transport.

I'm not trying to say that szlamany is wrong but I would suggest that you use the data types intended for the type of data you have unless you have a specific reason for doing otherwise. If the pragmatic option is to use text then use text but I would recommend against using text as the default option. That's the way I've always worked and never had an issue with it.

[cory_jackson]

Thanks. And I am using data types. I'm just trying to figure out if it's all being converted to text. Yes I understand it's all ultimately binary. But if I look at the series of bytes in memory my birthday looks different depending on the datatype.

I'm not expressing myself well. Let me ask another way. If I have a DateTime variable in my program and a DoB column on the Db table with a DateTime type and I send it in an INSERT is it converted to string then cast back into DateTime?

[jmcilhinney]

Thanks. And I am using data types. I'm just trying to figure out if it's all being converted to text. Yes I understand it's all ultimately binary. But if I look at the series of bytes in memory my birthday looks different depending on the datatype.

I'm not expressing myself well. Let me ask another way. If I have a DateTime variable in my program and a DoB column on the Db table with a DateTime type and I send it in an INSERT is it converted to string then cast back into DateTime?

No. When I said it would be silly to do so in my previous post, I was talking about the system doing it automatically. Systems are generally designed to be as efficient as possible so converting a number to more numbers and then back again to the original number would be a silly thing to do.

[cory_jackson]

So it is being transmitted as DateTime. OK. Thanks! You have been a ton of help.

[JuggaloBrotha]

Forgive me confusing things. I have muddied the water with my confusing terms. I started doing Access where there were 'records' and 'fields' which is conceptually more sensible to me. IE that a record contains fields whereas the row and column concept is more like a coordinate system. IOW in my mind the table had columns and a row does not. Can't have a column of one value to my old way of thinking. When I visualize a column I see multiple values aligned vertically, not one. Szlamany is completely right. Sorry about that.

Szlamany that's a perfect little solution you have and I'm going with that.

JuggaloBrotha thanks for your solution as well.

I have one more unimportant question. I'm just curious. I created a simple test application for this but when I tried to cast it to a Boolean it failed. I changed the query to be "true" and "false" and that worked. So am I to understand it's returning a text datatype?

FYI Access and Sql Server/MySQL/Oracle have different terms for the same thing:
Access : Sql Server
Record = Row
Field = Column

Both are the same coordinate system.

I've never used BOOLEAN data type in MS SQL. I've seen people with ACCESS-backgrounds make use of it.

I always go with a Y/N field since most UI elements work well with that.

In Sql Server a Boolean is the bit datatype, .Net converts the Sql bit to Boolean for you.

[cory_jackson]

My initial question has been resolved so I'm marking it as such. However I'd love for someone to explain how a text SQL command can contain non-text data types. Maybe I'll post another message.

[szlamany]

Yeah - post another message...