Books/Online Resources for SQL Server 2005?

Could some body suggest some good books and online resources for:

1. T-SQL Intermediate level/Advanced

2. SQL Server 2005

Thanks a bunch

Csharplearner

Hi,

Here are some links:

T-SQL/SQL Server Books - List of books with customer reviews

sql

Books Online July 2006 issue

The SQL Server Books Online was working installed on my workstation fine until this morning. Now when I open it up it does not show me T-SQL reference with all the commands and functions. Yesterday I was on the MSDN website using the Books Online. Could some pointers in the program been corrupted? I have uninstalled and reinstalled Books Online to no avail.

Please help.

Thanks,

Fred

Hi Fred,

When you open Books Online on your local machine, do you see a Contents tab (typically located on the left-hand side of the page)? If no, click the Contents option on the top tool bar. This should reopen the Contents tab that has been closed or hidden.

2. If that doesn't resolve your problem, can you please provide more information. For example, can you find T-SQL Reference topics by using the Index or Search tabs?

Regards,

Gail

|||

Hi Gail,

Yes, the Contents tab is there. When I expand "SQL Server 2005 Books Online" entry in the Contents tab and then expand "SQL Server Language Reference" it shows only "Transact-SQL Reference". It used to show more. Then when I expand "Transact-SQL Reference" it only shows "System Tables (Transact-SQL)". It used to list all the statements and functions.

Thanks,

Fred

|||

Hi Fred,

Okay, this sounds like maybe a filter was accidently set that is limiting the returned topics. At the top of the Contents tab, there's a "Filtered By" drop down box. Make sure the filter is set to either 'unfiltered' or 'SQL Server 2005', or 'SQL Server Database Engine'.

That should resolve the problem, but if it doesn't let me know.

Gail

|||

Hi Gail,

Thanks, that fixed the problem.

Fred

Books on T-SQL

Hi Can anyone suggest me a good book on T-SQL for SQL Server 2005? I preferably need both a Hardcopy and a softcopy (any link which covers in depth T-SQL for YUKON will do). Same for Hardcopy also.

Thanks in Advance

Shaun

I haven't actually read it, but the O'Reilly books are generally very good, and they have released on one SQL 2005:

http://www.oreilly.com/catalog/progsqlsvr/

Also, Microsoft has some good books and courses online:

http://msdn.microsoft.com/sql/learning/default.aspx

HTH|||

For me Books OL is enough for all T-SQL questions and syntax issues.

I was looking for some good book for 2k5 but not many books are out yet.

|||? I don't think any are out yet. Itzik Ben-Gan and a couple of others have two books coming out in a few months, from Microsoft Press (part of the "Inside SQL Server" series). You might have to make due with BOL until that one is released. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Shaun Michael@.discussions.microsoft.com> wrote in message news:3049dd63-4c3c-42a8-9e31-57ee97c32f4e@.discussions.microsoft.com... Hi Can anyone suggest me a good book on T-SQL for SQL Server 2005? I preferably need both a Hardcopy and a softcopy (any link which covers in depth T-SQL for YUKON will do). Same for Hardcopy also. Thanks in Advance Shaun|||I've seen some books out, including the one in the link above. Some were out in Novemeber, but not official ones.|||? I'm not sure what you mean by "official". The only official documentation is Books Online. I don't recommend the book in my signature because the coverage of T-SQL isn't all that deep -- we covered the new features only. Aside from the four books targetted at the beta editions (Beauchemin et al, Otey, DeBetta, and Scalability Experts), all of the other books currently on the market are targetted at RTM, as far as I'm aware. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Shughes@.discussions.microsoft.com> wrote in message news:879be8bc-3533-4242-a45d-fc56a35ec105@.discussions.microsoft.com...I've seen some books out, including the one in the link above. Some were out in Novemeber, but not official ones.

Books

Hello
Can You advise me some books about merge replication, with alternate sync
partner and maybe republishing.
Plus lots of T-Sql scripts with comment
Best Regards
Wojciech Znaniecki
I am working on a merge replication book. I am not sure when it will be
done. Target date is March of this year.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message
news:cnhr53$coh$1@.atlantis.news.tpi.pl...
> Hello
> Can You advise me some books about merge replication, with alternate sync
> partner and maybe republishing.
> Plus lots of T-Sql scripts with comment
> Best Regards
> Wojciech Znaniecki
>
|||Uytkownik "Hilary Cotter" <hilary.cotter@.gmail.com> napisa w wiadomoci
news:uiDk6bXzEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am working on a merge replication book. I am not sure when it will be
> done. Target date is March of this year.
> --
> Hilary Cotter
I was counting that Your book is already available. Your advices helped
me a lot.
But I'm afraid that March is too late for me
Maybe some other good books ?
Best Regards
Wojciech Znaniecki

Book suggestions on T-SQL

Hi All,
I am new to SQL Server but have been doing database programming since last 3 years. I recently attended MOC (Microsfot Official Curriculum) training on SQL Server and have started to use at my company. I am comfortable with SQL but want to dig deeper into T-SQL side. I searched on the Internet but not many good books available in that either they are ranked very low or are very old i.e. written around 1999/2000 or covers SQL Server 2000 as a whole. Can anybody suggest me any T-SQL book which was written recently and focuses purely or majorly on T-SQL?

Thanks to all for your time and advice in advance.

Regards:
PrathmeshKen Henderson. Some of the stuff is from the Guru's Guide to Transact SQL is a little dated but you will learn a lot. He has 2 books after that one I have been meaning to get to that are a little more up to date and I have heard are quite excellant.|||http://www.sqlteam.com/store.asp|||Thanks guys for the suggestions. Ken Henderson's books
1. Guru's Guide to T-SQL
2. Guru's guide to Stored Procedures, XML, HTML

seem to be good. Book 2 is slightly newer than book 1, but seem to have good material coverage. I also went through amazon reviews and TOC and overall the book seems good. As Thrasymachus pointed out, Book 1 is a bit out of date, but seems to be a good starting point.

Once again, thanks for your tips.

Regards.
Prathmesh

BLOB in T-SQL

Hi,

can anyone help ,me out here with some design consideration reguarding importing of BLOB data to a SQL server 2000 using T-SQL statements?

I want to make an import of some documents which are stored in a Access database, to an Ms SQL server 2000. The documents are stored in the access database as a OLE Object, by now I thought of using the base64String function to convert the data from the access field and write it to the T-SQL statements which will written in a text batch file. And then I apply the SQL Convert function something like:

INSERT INTO testBin VALUES(convert(image,'base64sting_encoded'))
go

Does this work? Is it correct what I am doing?

Thanks.

one rule i remember about handling blob

is that it needs to be on a separate filegroup to avoid

fragmentation

|||

Access OLE Object column data goes directly into image in SQL Server. So I am not sure why you are doing the convert and it may not really produce the correct results. See below links for more details on Access migration:

http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

http://www.microsoft.com/sql/solutions/migration/default.mspx

See the link below for some great information on when to store BLOBs in the database:

http://research.microsoft.com/research/pubs/view.aspx?type=technical+report&id=1089

|||that was also what i have in mind though i hesitate to say

Blank vs. Space

Hello, it seems to me that T-SQL is having a hard time
distinguishing a blank from one or more spaces. When I run
the statements below, the SELECTs always give 1
as a result. This seems completely wrong to me.. or am I
missing something?
CREATE TABLE TempTable (Value VARCHAR(10))
INSERT INTO TempTable (Value) VALUES ('')
SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
using Query Analyzer on SQL Server 2000 sp3a
Thanks,
SteveSQL Server does not take in mind spaces in the right side when using operato
r
= to do the comparison.
Example:
select 1 where space(0) = space(10)
go
try:
SELECT COUNT(*) FROM TempTable WHERE datalenght(Value) = 0
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
go
AMB
"Steve Deering" wrote:

> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||This is, I believe, according to the ANSI SQL definition. You can use LIKE i
nstead, as LIKE is
sensitive to trailing spaces:
SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Trailing spaces are dropped so this is working as advertised.
Try this and you'll see what I mean.
declare @.value varchar(10)
set @.value = ' '
SELECT len(@.value)
...returns 0
This:
set @.value = ' b '
SELECT len(@.value)
...returns 2, the leading space and 'b'
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Tibor,
I think it is right except for the first one.
Example:
select 1
from
(
select space(0) union all select space(1) union all select space(2)
) as t(colA)
where colA like space(0);
AMB
"Tibor Karaszi" wrote:

> This is, I believe, according to the ANSI SQL definition. You can use LIKE
instead, as LIKE is
> sensitive to trailing spaces:
>
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Deering" <SteveREMOVE@.vin.com> wrote in message
> news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
>
>|||Indeed. Thanks Alejandro... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CCBDF7B5-B5B2-459D-9DA8-339EFC53AA59@.microsoft.com...
> Tibor,
> I think it is right except for the first one.
> Example:
> select 1
> from
> (
> select space(0) union all select space(1) union all select space(2)
> ) as t(colA)
> where colA like space(0);
>
> AMB
> "Tibor Karaszi" wrote:
>|||Huh. Apparently, it's similar for JOINs, as if the values get RTRIM'd:
USE Northwind
GO
CREATE TABLE TempTable (Value VARCHAR(10))
GO
INSERT INTO TempTable (Value) VALUES ('abc') -- Insert a ZLS
INSERT INTO TempTable (Value) VALUES ('abc ')
INSERT INTO TempTable (Value) VALUES ('abc ')
GO
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' -- ZLS
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 1 space
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 2 spaces
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' + Space(5) -- 5 spaces
GO
SELECT * FROM TempTable t inner join
(select value from TempTable where Value='abc ') t1 on t.value = t1.value
WHERE t.Value = 'abc '
DROP TABLE TempTable
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
One of the other code monkeys here posed an interesting question, though we
can't devise an example: What if in some implementation, the trailing
spaces have significant meaning, so that "bob" should not match "bob "?
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||My name isn't "bob", it's "bob ".
Do you think that you can legally change your name to add a few trailing
spaces. :-)
When is a door not a door?
When it's a "door ".
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:euQJpVGQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> One of the other code monkeys here posed an interesting question, though
> we
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob
> "?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||You can use LIKE operator in a join condition.
Example:
select
*
from
(
select 'Microsoft'
) as t1(colA)
inner join
(
select 'Microsoft' + space(1)
) as t2(colA)
on t1.colA like t2.colA
go
AMB
"Mike Labosh" wrote:

> One of the other code monkeys here posed an interesting question, though w
e
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob "
?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>

Bit fields in T-SQL Queries?

I'm creating a query that filters on a bit field that I'm using to represent a boolean value. My query looks something like this:

SELECT * FROM MYTABLE WHERE ACTIVEORDER = 1

And that query works okay--I get all records where 'active order' is true.

Here's my problem--I'd like to make this query more readable by using TRUE or FALSE, instead of 1 or 0. I thought that I had read in BOL that T-SQL can convert the string values TRUE and FALSE to 1 and 0. But I can't get that to work in my query.

Is there a way to use TRUE and FALSE as the filter value for a bit field in a T-SQL query, rather than 1 and o? If so, what does the query look like? Thanks.

AFAIR no, it's not possible. Moreover, I highly recommend you not to mix different languages - it is only confusing. In other words, your query may look more readable for you, but it may look completely enigmatic for another one at the same time.|||Thanks--that makes sense. Just for my own curiousity, any idea why T-SQL doesn't have a boolean type?|||Why, it has - a bit type is perfectly suitable for such a role
For example, there may be some strictly defined things in ANSI SQL which MSFT has to follow. Also, the SEQUEL language (ancestor for current SQL) was developed in the past when every byte in the listing was thrice-counted, so no wonder they decided to use short 1/0 instead of literal constants.|||

--Sure there's a way to make it more readable.

--Just put this at the top of the query:

declare @.True bit,@.False bit

select @.True = 0, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

--Sorry, I fat-fingered this query. The below is corrected:

declare @.True bit,@.False bit

select @.True = 1, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

|||

So, this works when BOTH True and False are equal to zero?

select @.True = 0, @.False = 0

--then use:

select * from MyTable where ActiveOrder = @.True

|||

Ennor wrote:

there may be some strictly defined things in ANSI SQL which MSFT has to follow.

None of the SQL Standard says BOOLEAN is one of the datatype in your database.

It says,

1. The value True/False are reserved words as per ANSI Standard

2. Boolean is not a valid datatype for your Table/SQL quries

3. Boolean/Truth values should be TRUE | FALSE | UNKNOWN

4. You can't use the TRUE/FALSE on your query.

|||

You can try

select case when activeorder=1 then 'TRUE'

case when activeorder=0 then FALSE'

end

from mytable

|||

Sorry, I fat fingered that one:

select @.True = 1, @.False = 0