sqlserver2000 date: null

Showing posts with label null. Show all posts

Monday, March 19, 2012

Inconsistent/Missing Information on Bit Special Datatype

Hello all,
I did some research trying to find an answer but was unsuccesessful.

Here's my question, does the bit special datatype support a null value in SQL 2000/2003?

I found an article on SQL 7.0 dated Mar 2001 by Sergey Vartanyan that states:

"Bit datatype is usually used for true/false or yes/no types of data, because it holds either 1 or 0. All integer values other than 1 or 0 are always interpreted as 1. One bit column stores in 1 byte, but multiple bit types in a table can be collected into bytes. Bit columns cannot be NULL and cannot have indexes on them."

On the other hand, when I look out at Microsoft's MSDN site I find the following in regards to SQL 2000:

"Consists of either a 1 or a 0. Use the bit data type when representing TRUE or FALSE, or YES or NO."

There is also this reference to bit for Transact SQL:
"Transact-SQL Reference
bit
Integer data type 1, 0, or NULL."

My personal opinion is if you require a 'yes/no' field, you wouldn't want to allow NULLs.

My reason for asking is I'm migrating from Access to SQL and within the Access tables some of the fields are YES/NO datatype but have Null values in some of the records.

One last thing, I know I can set it to a default of 0 or 1, but since I didn't write the application, I don't want to second guess the programmer. If newer versions of SQL will support NULL on the bit datatype, then it makes things easier for me.

Thanks in advance for any and all help.
T. MullinsThat is a $64,000 dollar question.

BOL states:

"Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on."

So it would appear that a bit value can take up as little as 1 bit of space. But bit values can clearly store NULLs:
--------
declare @.BitTest bit
Print @.BitTest

set @.BitTest = 0
select @.BitTest

set @.BitTest = null
select @.BitTest
---------
So how is it possible to store three possible states (1, 0, Null) in a single computer bit?

Obviously something else is going on behind the scenes, but I've never seen an explanation for it either.

blindman|||Originally posted by blindman
That is a $64,000 dollar question.

BOL states:

"Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on."

So it would appear that a bit value can take up as little as 1 bit of space. But bit values can clearly store NULLs:
--------
declare @.BitTest bit
Print @.BitTest

set @.BitTest = 0
select @.BitTest

set @.BitTest = null
select @.BitTest
---------
So how is it possible to store three possible states (1, 0, Null) in a single computer bit?

Obviously something else is going on behind the scenes, but I've never seen an explanation for it either.

blindman

Hey Blindman,
Thanks for the input. I guess I'll fall back on providing a default value if no data is passed in.

Wednesday, March 7, 2012

Inclusive WHERE expression

I have a column YearGiven (smallint, null). I need to be able to use a WHERE clause that will include all years. Something like this:

SELECT * FROM Documents WHERE YearGiven = *

I realize if I eliminate the WHERE clause entirely this will produce the desired result. However in my VB app my Select statement will have to take into account that the YearGivenTextBox used in the Full Text Search may be empty. This is not a problem if I can assign a value to the empy textbox variable that will include all years.

If you use @.YearGivenTextBox parameter, you could use following code:

Code Snippet

SELECT * FROM Documents WHERE YearGiven = @.YearGivenTextBox OR @.YearGivenTextBox IS NULL

--Also you could try

SELECT * FROM Documents WHERE YearGiven = @.YearGivenTextBox OR @.YearGivenTextBox = ''

If @.YearGivenTextBox not empty first part work, else second and SQL Server returns all rows.

|||

This would work:

SELECT * FROM Documents WHERE YearGiven IN (SELECT YearGiven FROM Documents)

You could also have a look at dynamic sql to build you sql string on-the-fly at run time and pass throught he parameters you need , here's a fantastic article on how to do that:

http://www.sommarskog.se/dynamic_sql.html

Ray

|||

Don’t use the logical expression against the variables on WHERE clause, it will force the engine to use Index Scan (even there is a possibility to use Index Seek). Always better check with if statement,

Don’t try to reduce the number of lines, always give the preference to the performance,

Code Snippet

if @.yeargiventextbox is null or @.yeargiventextbox = '' -- isnull(@.yeargiventextbox ,'') = ''

select * from documents

else

select * from documents where yeargiven = @.yeargiventextbox

|||

Thanks for suggestions. You have all given me some things to think about (especially Ray's link to the article on dynamic SQL). I am reconsidering my original strategy for how to work with the WHERE clause.

Manivannan, how would I implement the IF-ELSE code snippet you provided in my Visual Basic app? Would it be embedded in the main SELECT statement, or set apart by using WHERE 1 = 1 in the SELECT statement?

|||

Following up on the link provided above by Ray, I have been researching the concept of Dynamic SQL:

Dynamic Search Conditions in T-SQL
An SQL text by Erland Sommarskog, SQL Server MVP.

http://www.sommarskog.se/dyn-search.html

Although it is a little complex for me with my current understanding of T-SQL, it does seem to address some of the problems I am facing and may provide a good learning experience (if nothing else.) He is filtering with 12 parameters. In my application I will have 9 (6 comboboxes and 3 textboxes) plus my basic WHERE CONTAINS full text search. So this may be comparable.

QUESTION 1: Based on the title of this thread, my initial question is: What does the expression WHERE 1=1 mean? Sommarskog is using his version of the Northwind DB and there is no column 1 that I can see. Is this a T-SQL convention for appending parameters to the main query? Or what?

QUESTION 2: Does this seem like a good approach for me based on the info I have provided above?

Thanks again for your patience and expertise in explaining these difficult concepts (for me) and providing workable options.

Here is Sommarskog's code example for the Dynamic Search stored procedure cited above:

Code Snippet

CREATE PROCEDURE search_orders_1
@.orderid int = NULL,
@.fromdate datetime = NULL,
@.todate datetime = NULL,
@.minprice money = NULL,
@.maxprice money = NULL,
@.custid nchar(5) = NULL,
@.custname nvarchar(40) = NULL,
@.city nvarchar(15) = NULL,
@.region nvarchar(15) = NULL,
@.country nvarchar(15) = NULL,
@.prodid int = NULL,
@.prodname nvarchar(40) = NULL,
@.debug bit = 0 AS

DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)

SELECT @.sql =
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City,
c.Region, c.PostalCode, c.Country, c.Phone,
p.ProductID, p.ProductName, p.UnitsInStock,
p.UnitsOnOrder
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID
WHERE 1 = 1'

IF @.orderid IS NOT NULL
SELECT @.sql = @.sql + ' AND o.OrderID = @.xorderid' +
' AND od.OrderID = @.xorderid'

IF @.fromdate IS NOT NULL
SELECT @.sql = @.sql + ' AND o.OrderDate >= @.xfromdate'

IF @.todate IS NOT NULL
SELECT @.sql = @.sql + ' AND o.OrderDate <= @.xtodate'

IF @.minprice IS NOT NULL
SELECT @.sql = @.sql + ' AND od.UnitPrice >= @.xminprice'

IF @.maxprice IS NOT NULL
SELECT @.sql = @.sql + ' AND od.UnitPrice <= @.xmaxprice'

IF @.custid IS NOT NULL
SELECT @.sql = @.sql + ' AND o.CustomerID = @.xcustid' +
' AND c.CustomerID = @.xcustid'

IF @.custname IS NOT NULL
SELECT @.sql = @.sql + ' AND c.CompanyName LIKE @.xcustname + ''%'''

IF @.city IS NOT NULL
SELECT @.sql = @.sql + ' AND c.City = @.xcity'

IF @.region IS NOT NULL
SELECT @.sql = @.sql + ' AND c.Region = @.xregion'

IF @.country IS NOT NULL
SELECT @.sql = @.sql + ' AND c.Country = @.xcountry'

IF @.prodid IS NOT NULL
SELECT @.sql = @.sql + ' AND od.ProductID = @.xprodid' +
' AND p.ProductID = @.xprodid'

IF @.prodname IS NOT NULL
SELECT @.sql = @.sql + ' AND p.ProductName LIKE @.xprodname + ''%'''

SELECT @.sql = @.sql + ' ORDER BY o.OrderID'

IF @.debug = 1
PRINT @.sql

SELECT @.paramlist = '@.xorderid int,
@.xfromdate datetime,
@.xtodate datetime,
@.xminprice money,
@.xmaxprice money,
@.xcustid nchar(5),
@.xcustname nvarchar(40),
@.xcity nvarchar(15),
@.xregion nvarchar(15),
@.xcountry nvarchar(15),
@.xprodid int,
@.xprodname nvarchar(40)'

EXEC sp_executesql @.sql, @.paramlist,
@.orderid, @.fromdate, @.todate, @.minprice,
@.maxprice, @.custid, @.custname, @.city, @.region,
@.country, @.prodid, @.prodname

|||

OK, Question # 1: What does WHERE 1 = 1 mean?

Here is what I found:

"If you're building a WHERE clause on the fly, and you don't know if there are any more expressions in the WHERE clause, then starting with 1=1 insures that you'll create a valid WHERE clause and the SELECT won't blow up. I don't recommend it but it works and it's quick."

"It's a standard way to have a "where" clause that it's always true."

"It allows the developers to not worry ... Normally used in dynamically generated SQL."

Question # 2: Is Dynamic SQL approach best for me?

Still don't know yet. I am trying a more standard Sproc approach and have gotten some of it to work. I can query in Visual Basic using a sprock with 6 parameters. I don't know how to include my Full Text Search parameter into the sproc. I don't know how to use IF-ELSE when the parameter value is NULL (the TextBox is empty or Combobox is unselected). Here is my sproc and Visual Basic code that works:

Code Snippet

CREATE PROC usp_Advanced_Search

@.doctype nvarchar(10) = NULL,

@.year varchar(6) = NULL,

@.sex varchar(6) = NULL,

@.category nvarchar(10) = NULL,

@.agenum smallint = NULL,

@.agecat nvarchar(10) = NULL AS

SELECT FullDocuments.FullDocNo, FullDocuments.DocType, Details.Year

FROM FullDocuments

INNER JOIN Details ON FullDocuments.FullDocNo = Details.FullDocNo

WHERE DocType = @.DocType AND Year = @.Year AND sex = @.sex

AND category = @.category AND agenum = @.agenum AND agecat = @.agecat

Code Snippet

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click

Dim conn As New SqlConnection("Data Source=OFFICE\FULLTEXTSEARCH;Initial Catalog=ECR;Integrated Security=True")

Dim Command As New SqlCommand("usp_Advanced_Search", conn)

Command.CommandType = CommandType.StoredProcedure

Dim SelectedDocType = DocTypeComboBox.Text.ToString

Dim SelectedYear = YearTextBox.Text.ToString

Command.Parameters.Add("@.DocType", SqlDbType.VarChar, 10)

Command.Parameters("@.DocType").Value = DocTypeComboBox.Text.ToString

Command.Parameters.Add("@.Year", SqlDbType.VarChar, 6)

Command.Parameters("@.Year").Value = YearTextBox.Text.ToString

Command.Parameters.Add("@.Category", SqlDbType.VarChar, 12)

Command.Parameters("@.Category").Value = CategoryComboBox.Text.ToString

Command.Parameters.Add("@.Sex", SqlDbType.VarChar, 6)

Command.Parameters("@.Sex").Value = SexComboBox.Text.ToString

Command.Parameters.Add("@.AgeNum", SqlDbType.SmallInt)

Command.Parameters("@.AgeNum").Value = AgeNumTextBox.Text.ToString

Command.Parameters.Add("@.AgeCat", SqlDbType.VarChar, 8)

Command.Parameters("@.AgeCat").Value = AgeCatComboBox.Text.ToString

Dim adapter As SqlDataAdapter = New SqlDataAdapter()

adapter.SelectCommand = Command

Dim ds As New DataSet()

conn.Open()

adapter.Fill(ds)

conn.Close()

DataGridView1.DataSource = ds.Tables(0)

End Sub

I have tried to include a FullTextSearch Parameter in my sproc like this:

Code Snippet

CREATE PROC usp_Advanced_Search3

@.doctype nvarchar(10) = NULL,

@.year varchar(6) = NULL,

@.sex varchar(6) = NULL,

@.category nvarchar(10) = NULL,

@.agenum smallint = NULL,

@.agecat nvarchar(10) = NULL AS

DECLARE @.SearchTerm NVARCHAR(100)

SET @.SearchTerm ='SearchTextBox.Text'

SELECT FullDocuments.FullDocNo, FullDocuments.DocType, Details.Year

FROM FullDocuments

INNER JOIN Details ON FullDocuments.FullDocNo = Details.FullDocNo

WHERE DocType = @.DocType AND Year = @.Year AND sex = @.sex

AND category = @.category AND agenum = @.agenum AND agecat = @.agecat AND CONTAINS(SectionText, 'SearchTerm')

I then added this code to the VB app:

Command.Parameters.Add("@.SearchTerm", SqlDbType.NVarChar, 100)

Command.Parameters("@.AgeCat").Value = SearchTextBox.Text.ToString

The VB solution builds successfuly but when I try the Full Text Search I get this error:

Procedure or function usp_Advanced_Search has too many arguments specified.

Any specific suggestions or code would be greatly appreciated.