How do I make it use my index?

Hello!

I have two tables

users and pictures.

table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid

when I do this query:

"select userid from pictures where userid=123"

then It will do a clustered index seek

But If I do any of those:

"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"

It will do a clustered index scan.

How can I force it to seek my index instead of scan?

Thanks!do you have some where clause at the end of the query? when not, then the plan created by sql server is perhaps really the best!
if the where clause shrinks the number of rows in t1 to a fraction of total table row count, you could use

option(loop join)

at the end of the query. this should solve your problem

How do I Hide Databases from External Users?

I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.

Thanks.RE:
Q1 I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

Q2 Although I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access -

Q3 but each of them when they enter my SQL Server, should not even know that the other databases exist. Thanks.

A1 {What was meant is not 100% clear; however, bestowing "[user rights for an external user to connect to SQL Server]" would generally be accomplished within Enterprise Manager, *Query Analyser, osql, isql, or programmatically, i.e.(via an API: ADO, CLib, DMO, OLEDB, etc.,); but not via the Client Network Utility (at least not directly)?}

A2 One may meet such requirements by designing the user application(s), i.e.(the ones the users will use with their respective DBs), such that other DB catalogs are not displayed. (This is arguably probably one of the better means of meeting such a requirement.)

Note: Various "all encompassing" implementation approaches may entail significant negative consequences. For example this includes many implementations that:
a Deny users Select and Exec rights to certain objects in the Master DB.
b Modify / add objects to the Master DB (this option especially, is NOT recommended).
c Require special connectivity software that supports the desired feature set (custom made ODBC, Sql Server, OLEDB, etc., drivers).

A3 You may wish to verify that this is in fact an important requirement. (That is, one that is worth the ramifications of the means chosen to implement a design that will meet the requirement.) As noted in A2, maintainability and / or functionality may be impaired with some kinds of "DB hiding" implementation approaches.

--

* The following Special Stored Procedures are supported in MS Sql Server 2k (execute from QA, etc.) for managing security:
sp_addalias
sp_addapprole
sp_addgroup
sp_addlinkedsrvlogin
sp_addlogin
sp_addremotelogin
sp_addrole
sp_addrolemember
sp_addserver
sp_addsrvrolemember
sp_adduser
sp_approlepassword
sp_change_users_login
sp_changedbowner
sp_changegroup
sp_changeobjectowner
sp_dbfixedrolepermission
sp_defaultdb
sp_defaultlanguage
sp_denylogin
sp_dropalias
sp_dropapprole
sp_dropgroup
sp_droplinkedsrvlogin
sp_droplogin
sp_dropremotelogin
sp_droprole
sp_droprolemember
sp_dropserver
sp_dropsrvrolemember
sp_dropuser
sp_grantdbaccess
sp_grantlogin
sp_helpdbfixedrole
sp_helpgroup
sp_helplinkedsrvlogin
sp_helplogins
sp_helpntgroup
sp_helpremotelogin
sp_helprole
sp_helprolemember
sp_helprotect
sp_helpsrvrole
sp_helpsrvrolemember
sp_helpuser
sp_password
sp_remoteoption
sp_revokedbaccess
sp_revokelogin
sp_setapprole
sp_srvrolepermission
sp_validatelogins|||With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?|||Originally posted by Paul Young
With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?

Thank you for your posts :)

I am using EM to connect|||Great, what will your users use to connect?

If your users will use EM or Query Analyzer then they have enough to see the diffrent DBs with little to no effort. A knowlegable user can even see system tables via I/OSQL. Past that, in ODBC a use will still be able to get a list of DB's if they specify a database when setting up the DSN.sql

How Do I Fix This Proc?

In a multi-user environment, this proc is expected to return distinct values
for concurrent users - but it doesn't, some duplicates are returned. I
thought that the 'BEGIN TRAN/COMMIT' would provide the required locking - I'
m
guessing that's where I went wrong. How do I fix this proc? The DDL for the
tables involved is included.
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId
WHERE (tableName = N'well')
BEGIN TRAN
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
CREATE TABLE dbo.tbTableMaxId
( tableName nvarchar(50) NOT NULL,
maxId int NOT NULL,
PRIMARY KEY (tableName)
)
CREATE TABLE dbo.well
( wellId int NOT NULL,
wellName nvarchar(50) NULL,
PRIMARY KEY (wellId)
)
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc."Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> In a multi-user environment, this proc is expected to return distinct
> values
> for concurrent users - but it doesn't, some duplicates are returned. I
> thought that the 'BEGIN TRAN/COMMIT' would provide the required locking -
> I'm
> guessing that's where I went wrong. How do I fix this proc? The DDL for
> the
> tables involved is included.
>
Basically strict serialization of the whole procedure will be required to
make this correct, so I hope you don't have to support concurrent inserts.
Which is the main reason why IDENTITY columns exist and you shouldn't try to
emulate them with custom code.
David
Here is your fix:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
BEGIN TRAN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END|||David,
Thank you for your response. I've modified your solution to avoid locking
the well table as follows:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
BEGIN TRAN
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
SELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
I'll be testing this next w and will post the results here.
Thanks again,
Hal Heinrich
VP Technology
Aralan Solutions Inc.
"David Browne" wrote:

> "Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> Basically strict serialization of the whole procedure will be required to
> make this correct, so I hope you don't have to support concurrent inserts.
> Which is the main reason why IDENTITY columns exist and you shouldn't try
to
> emulate them with custom code.
> David
> Here is your fix:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> BEGIN TRAN
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
> WHERE (tableName = N'well')
>
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> ELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
>
>
>|||Hello Heinrich!
I think, it would a better idea if you try to lock only ,row for N'well'
table,
I thin you should use an rowlock hint
"Hal Heinrich" wrote:
> David,
> Thank you for your response. I've modified your solution to avoid locking
> the well table as follows:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
> BEGIN TRAN
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
> WHERE (tableName = N'well')
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> SELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
> I'll be testing this next w and will post the results here.
> Thanks again,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
>
> "David Browne" wrote:
>|||"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:8A55A9E5-C188-4DCF-BDD9-656AD810C31B@.microsoft.com...
> Hello Heinrich!
> I think, it would a better idea if you try to lock only ,row for N'well'
> table,
> I thin you should use an rowlock hint
>
Why? And what row do you propose locking?
David

How do I exclude carriage returns as part of a IS NULL exclusion clause...

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]

Cheers - AndyThis is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:LIKE '%[!-~]%' to see if there are any printable characters (this expression ignores whitespace).

-PatP|||Very interesting and very cool (speaking purely as a geek!).

I got it to work by doing this:

and field like '%[a-z0-9]%'

This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

Thanks very much for your help!

Andy|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!

-PatP