Database

31-Oct-14 6:03

Now you've cross-posted.
http://www.codeproject.com/Messages/4934084/Re-SSIS-package.aspx[^]

Re: SSIS package

Richard Deeming31-Oct-14 6:18

Richard Deeming

31-Oct-14 6:18

PIEBALDconsult wrote:
Now you've cross-posted.

Only after you told him not to post it in the C# forum. D'Oh! | :doh:

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SSIS package

PIEBALDconsult31-Oct-14 6:33

Link to Microsoft Virtual System

31-Oct-14 6:33

The C# forum may well be the correct forum. He hasn't answered my question there.

Shweta N Mishra31-Oct-14 3:41

Shweta N Mishra

31-Oct-14 3:41

Hi
Does anybody have any idea about the system/Link, Where Microsoft provides an Environment and we can do experiments , Work with database, run our query etc.

Re: Link to Microsoft Virtual System

Vijay Gill31-Oct-14 3:47

Vijay Gill

31-Oct-14 3:47

Do you mean Azure?

Re: Link to Microsoft Virtual System

Shweta N Mishra31-Oct-14 5:21

Shweta N Mishra

31-Oct-14 5:21

Yes thanks got it.

Uncertain on What to Use as Foreign Key of a Table

ASPnoob30-Oct-14 5:19

ASPnoob

30-Oct-14 5:19

Hi all, I am trying to design a database to hold data for a job portal. Basically I have a database called SiteUsers which contains five tables (UsersTable, JobSeekersTable, ResumesTable, EmployersTable, and JobsTable). The following are the table schema for UsersTable, ResumesTable, and JobsTable.

The UsersTable has a Primary Key (PK) of UserID and a Foreign Key (FK) of Username.
The ResumesTable has a PK of ResumeID and the JobsTable has a PK of JobID.
The JobSeekersTable has a PK of Username.
The EmployersTable has a PK of Username.

The question I have is should the JobSeekersTable FK also be Username, or ResumeID, or a composite of Username and ResumeID. I think that the FK should be Username because it's unique but because it's not the primary key of the JobsTable I am uncertain. Also I did not want to use ResumeID as the FK in the JobSeekersTable because although it will be unique a job seeker can have more than one resume.

Whether you agree or disagree with my assumption that Username should be the PK and FK in the JobSeekersTable, please elaborate your answer . Thanks in advance for your help.

modified 30-Oct-14 12:35pm.

Re: Uncertain on What to Use as Foreign Key of a Table

PIEBALDconsult30-Oct-14 5:59

Re: Uncertain on What to Use as Foreign Key of a Table

30-Oct-14 5:59

0) You don't actually define a foreign key on a table
1) You may want to define a unique index on UsersTable, but don't use it as a foreign key
2) The other tables should be related to UsersTable on UserID, not on Username

Eddy Vluggen30-Oct-14 6:08

Eddy Vluggen

30-Oct-14 6:08

A foreign key is a primary key from another table. The "username" in the Users table (calling it userstable is redundant, all those things are tables and not chairs with little chance of confusing those) is not a foreign key - but it might be candidate-primary key.

Also, the artificial autonumber may be unique, but it does not mean that one can assign it blindly to be the primary key without asking oneself what the user will be using to uniquely identify a record.

You should look into normalization.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Uncertain on What to Use as Foreign Key of a Table

ASPnoob30-Oct-14 6:34

ASPnoob

30-Oct-14 6:34

Hi, thanks for responding. You said "calling it userstable is redundant", that was a typo.

plsql

Member 1118871528-Oct-14 21:28

Member 11188715

28-Oct-14 21:28

What r the works u r doing in the IT company..I want to become plsql developer..please share ur works in brief..

Re: plsql

Mycroft Holmes28-Oct-14 22:03

Retrieve Data Using Comma Separated List

28-Oct-14 22:03

The first thing you should do is learn to use a keyboard!

Never underestimate the power of human stupidity
RAH

Re: plsql

PhilLenoir29-Oct-14 3:04

PhilLenoir

29-Oct-14 3:04

I foresee a return to single letter variables. Bring back the ZX80!

Life is like a s**t sandwich; the more bread you have, the less s**t you eat.

ASPnoob28-Oct-14 20:10

ASPnoob

28-Oct-14 20:10

Hi all,
Suppose I have a database table named JobsTable and among the fields in the table are JobDescriptions, and JobTitle. How would I write my Where clause if users can get all the JobTitle where the JobDescriptions field contains any word in the comma separated list they entered?

I tried the Like keyword but it is not doing what I want. Thanks in advance for your help.

Re: Retrieve Data Using Comma Separated List

Mycroft Holmes28-Oct-14 22:01

Re: Retrieve Data Using Comma Separated List

28-Oct-14 22:01

I have a function (every dev does) which splits a string and returns a table with ID and Item, you could then join the split with your data table

SQL

Select *
From Table T
inner join fn_Split(@List,',') l on L.item = T.FieldName

SQL

ALTER   FUNCTION [dbo].[fn_Split]
 (@List  varchar(8000), @Delimiter char(1))

RETURNS @Results table
 (Item varchar(8000),ID int Identity(1,1))

AS

begin
 declare @IndexStart int
 declare @IndexEnd int
 declare @Length  int
 declare @Word  varchar(8000)

 set @IndexStart = 1
 set @IndexEnd = 0

 set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','

--Get rid of any tabs or returns
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')

while @IndexStart <= @Length
begin
	set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)
		
	If @Delimiter = char(32) 
		set @IndexEnd = charindex(Space(1), @List, @IndexStart)

	if @IndexEnd = 0
		set @IndexEnd = @Length + 1

	set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
	set @IndexStart = @IndexEnd + 1

	INSERT INTO @Results(Item)
	SELECT @Word
end

return
end

Never underestimate the power of human stupidity
RAH

Shweta N Mishra29-Oct-14 1:23

Shweta N Mishra

29-Oct-14 1:23

You can also convert comma separated list to rows(column to rowConverting comma separated data in a column to rows for selection[^]) and then you can run the like query against it.

Re: Retrieve Data Using Comma Separated List

Mycroft Holmes29-Oct-14 3:13

Advice on Perfomance Turning of the Query

29-Oct-14 3:13

Yeah I probably snaffled that code from someone in the 90s and have not looked at it since Blush | :O

, I'm certain there is a better way of splitting the items.

Never underestimate the power of human stupidity
RAH

Vimalsoft(Pty) Ltd28-Oct-14 7:27

Vimalsoft(Pty) Ltd

28-Oct-14 7:27

hi guys

I need some advice , i have a Query that runs very long because of the following lines of Code , i know you dont understand my data or table structure , you can just advice on a High Level

   ( CASE WHEN ( SELECT TOP 1
                      ATTRIB_CODE
            FROM      SDE.[NOTIFICATION] WITH ( NOLOCK )
            WHERE     ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = LP.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                        AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      )
                      AND ARCHIVE_DATE IS NULL
          ) LIKE '00090009%'
     THEN ( SELECT TOP 1
                      ATTRIB_CODE COLLATE SQL_Latin1_General_CP1_CI_AS
            FROM      SDE.[NOTIFICATION] N WITH ( NOLOCK )
            WHERE     N.ARCHIVE_DATE IS NULL
                      AND N.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      AND N.LIS_KEY  COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      AND N.ATTRIB_CODE LIKE '00090009%'
                      AND N.ARCHIVE_DATE IS NULL
                      AND V.ARCHIVE_DATE IS NULL
          )
     ELSE ISNULL(( SELECT TOP 1
                          ATTRIBUTE_CODE
                   FROM   SDE.VALUATION WITH ( NOLOCK )
                   WHERE  ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                            AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                          )
                          AND VAL_STATUS_ID = 2
                          AND ARCHIVE_DATE IS NULL
                 ), '')
END ) AS ATTRIBUTE_CODE ,

thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

PIEBALDconsult28-Oct-14 8:04

28-Oct-14 8:04

SQL Server? Did you check the execution plan?

Eddy Vluggen28-Oct-14 8:59

Eddy Vluggen

28-Oct-14 8:59

Vuyiswa Maseko wrote:
you can just advice on a High Level

There is a high level checklist for performance here[^].

I'd recommend against optimizer hints unless you can explain how SQL server handles locking. I'd also recommend on removing the collation-checks; it would be set once for the server, and not be repeated in each query as that takes extra time.

It also looks a lot like logic that could be run when inserting/updating the record. If that is not helping enough, then look into partitioning the table.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes28-Oct-14 12:59

28-Oct-14 12:59

As a last resort look into Parameter Sniffing[^], I know it sounds like a perversion but it can be an issue.

Basically the work around is to create a set of local variables for each parameter passed into the proc and use the local variables in the proc and not the parameters.

Never underestimate the power of human stupidity
RAH