Database Discussion Boards

Re: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?)

JChrisCompton30-Apr-18 7:35

30-Apr-18 7:35

Thanks again. I believe the term I was looking for is "offrow pages".

Here's a recent (Feb 2018) link that talks about it briefly then takes the undocumented DBCC IND command really deep into the RowID and beyond SQL Server Row Data Linking to Off Row Data[^] in case you're interested.
I enjoyed it but ~~was a little~~ got lost Smile | :)

Re: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?)

Eddy Vluggen30-Apr-18 8:13

Eddy Vluggen

30-Apr-18 8:13

Cool, thanks for the link Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Re: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?)

Jörgen Andersson25-Apr-18 2:15

Jörgen Andersson

25-Apr-18 2:15

JChrisCompton wrote:
The index isn't just for lookup speed, it is a unique index (same functionality as a unique constraint unless I'm missing something... I'm more of an 'app dba' than a 'real DBA') to enforce referential integrity because... referential integrity.

You're not missing anything, but you need to differentiate the purpose.
A key, (Unique or primary) needs a unique index for its function, and while it's enough to create a unique index to enforce the uniqueness of a column(s) you should really create a unique (or primary) key to show the intent if nothing else.

From the help file
Creating a unique index guarantees that any attempt to duplicate key values fails. There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE constraint on the column when data integrity is the objective. This makes the objective of the index clear. (emphasis mine.)

There's also another slight difference, on a unique index you can actually add the option "Ignore duplicate values", this is not allowed for a constraint.

JChrisCompton wrote:
So I came up with the idea of a Clustered Identity as the 'physical sorting field'

This is a good idea, but it would need to be the primary key in that case.
Which doesn't matter at all, from a relational point of view there's no difference between a primary key and a unique key. It's only about how the data is stored in the case of clustered tables and magnetic tapes. Smile | :)

JChrisCompton wrote:
Someone mentioned the volume being low. Yes, last month was 3,000 but this will be expanded from 1 to 5 categories. This could cause 3,100 entries next month or 31,000 entries

To put it in relation, at my job we insert approximately 300000 rows every week on a clustered table with 300 columns and a semi-random natural key. It takes a couple of minutes.

Wrong is evil and must be defeated. - Jeff Ello

Re: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?)

JChrisCompton25-Apr-18 5:47

JChrisCompton

25-Apr-18 5:47

Just so you know (in the event that I ask a question here again) those inserts would be against the same database server which supports thousands of real-time users. I was experimenting because it seemed needlessly inefficient to have a clustered char(36) as the key. WTF | :WTF:

Turns out that all my questions may be moot (aside from my own learning) Sigh | :sigh:

For review exec sp_helpindex <table name> gives the following description

SQL

table #1 
clustered, unique, primary key located on PRIMARY	guid column

table #2
clustered located on PRIMARY				the int column
nonclustered, unique, primary key located on PRIMARY	guid column

There is no detectable difference on insert speed when table size is <100k.
There is a difference, but it seems trivial, when the table contains more than two million rows (14 second difference when inserting 300k rows in each table)

P.S.
Love the magnetic tape reference.

P.P.S.

> a clustered table with 300 columns and a semi-random natural key

Yikes!

Re: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?)

Jörgen Andersson25-Apr-18 10:49

Jörgen Andersson

25-Apr-18 10:49

The difference is bigger than you might think.

On table #1 you have one index, and on table #2 you have two indexes.
Insert performance is basically linear with the number of indexes[^].
So the fact that it doesn't take twice as long to do the inserts on table #2 means that your idea was quite correct after all. And this is on a narrow table, on a wide clustered table the difference would be bigger.

Wrong is evil and must be defeated. - Jeff Ello

Re: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?)

JChrisCompton26-Apr-18 4:17

JChrisCompton

26-Apr-18 4:17

One has twice the indices... well duh.

I should have thought of that... but I didn't Laugh | :laugh:

Thanks for sticking with me until I got it!

EXCEPTIONS INTO Equivalent for SQL Server

User 1205041720-Apr-18 4:59

User 12050417

20-Apr-18 4:59

Hi there,

Below is an Oracle statement 'EXCEPTIONS INTO' I'd like to replicate in SQL Server. Is there an equivalent type of statement in SQL Server?

ALTER TABLE tablename ADD (
PRIMARY KEY(a,b)
EXCEPTIONS INTO exceptionTable);

TIA

modified 20-May-21 21:01pm.

Re: EXCEPTIONS INTO Equivalent for SQL Server

Eddy Vluggen23-Apr-18 5:21

Eddy Vluggen

23-Apr-18 5:21

There isn't, AFAIK. If you want to insert, you wrap it in a transaction and insert. Rollback if there's any conversion-error. Fix the error, insert again. No exception-table Thumbs Up | :thumbsup:

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Re: EXCEPTIONS INTO Equivalent for SQL Server

User 1205041723-Apr-18 5:24

User 12050417

23-Apr-18 5:24

Thanks Eddy.

modified 20-May-21 21:01pm.

how to access temp table field in subquery?

joost.versteegen11-Apr-18 21:43

joost.versteegen

11-Apr-18 21:43

hi,
I get an error :
The multi-part identifier "e.ent_name" could not be bound.
when trying to access a field from a temp table in a sub-query.

SQL

declare @entities table(ent_id int, ent_name varchar(50), startDT datetime)

insert into @entities
select ent.ent_id, ent_name, prod_job.act_start_time_local
from job as prod_job
inner join ent as ent on prod_job.run_ent_id = ent.parent_ent_id
where prod_job.wo_id = 'B.100077779'

select * from @entities -- so far so good

select * from @entities as e
inner join
(
    select top(1) act_finish_time_local, oper_id from job as cleaningJob
    where cleaningJob.oper_id like '%'+ e.ent_name +'%' ----> error
    order by act_finish_time_local desc
)
as j on j.act_finish_time_local < e.startDT

how can i fix this?
Thanks

Re: how to access temp table field in subquery?

Victor Nijegorodov11-Apr-18 22:18

Victor Nijegorodov

11-Apr-18 22:18

Have a look at sql - What is a 'multi-part identifier' and why can't it be bound? - Stack Overflow

Re: how to access temp table field in subquery?

Mycroft Holmes11-Apr-18 23:42

Mycroft Holmes

11-Apr-18 23:42

Reverse your operation

Populate the @Entities table as you are doing

change the select query
Select from Job
inner join on @Entities on - apply the filter here

Never underestimate the power of human stupidity
RAH

Re: how to access temp table field in subquery?

joost.versteegen11-Apr-18 23:53

joost.versteegen

11-Apr-18 23:53

thank you, i will try it

Re: how to access temp table field in subquery?

Richard Deeming19-Apr-18 2:34

Richard Deeming

19-Apr-18 2:34

Try using CROSS APPLY instead:
Making OUTER and CROSS APPLY work for you[^]

SQL

select * from @entities as e
CROSS APPLY
(
    select top(1) act_finish_time_local, oper_id 
    from job as cleaningJob
    where cleaningJob.oper_id like '%'+ e.ent_name +'%'
    and cleaningJob.act_finish_time_local < e.startDT
    order by act_finish_time_local desc
)
as j

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

Exporting Select statement values into csv with escape characters

indian1439-Apr-18 12:16

indian143

9-Apr-18 12:16

hi all,

I am exporting record-set of select statement into a csv, but when I am doing it, if the fields already have values with , and then that comma is going into the csv as it is, and because of it the import is becoming tough, I want to have an escape for those comma which are coming from the field values.

Any help would be grateful, thanks in advance friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Exporting Select statement values into csv with escape characters

Victor Nijegorodov9-Apr-18 20:27

Victor Nijegorodov

9-Apr-18 20:27

You could use the BCP command with the -t option. See bcp Utility
And there is a lot of other ways. Just google for something like MSSQL import into .csv

Reading csv file data using selecte statement

indian1439-Apr-18 6:50

indian143

9-Apr-18 6:50

Hi all,

I am trying to get all the columns and rows of a csv file using a select statement, when I am trying as in the below query, its retrieving all the rows and columns as one column and one row.

SELECT * FROM OPENROWSET(
   BULK 'D:\Users\Abdul\Provider.csv',
   SINGLE_CLOB) AS DATA;

I have tried in the following way

select *
into #T
from openrowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:\Users\Abdul;Extensions=csv;',
'select * from \Provider.csv') Test;

select *
from #T;

Its giving me the following error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Can any body please help me, am I missing anything in the above query, any help would be very much grateful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 9-Apr-18 13:16pm.

Re: Reading csv file data using selecte statement

Victor Nijegorodov9-Apr-18 8:37

Victor Nijegorodov

9-Apr-18 8:37

Did you try what was suggested in:
sql server - SQL Bulk import from CSV - Stack Overflow
Using OPENROWSET to import CSV files

Re: Reading csv file data using select statement

indian14311-Apr-18 9:18

indian143

11-Apr-18 9:18

hi,

I don't have permissions to run the open rowset and it needs the format file creation, I tried creating the format file, but some reason its not letting me do it.

I tried the same using the SSIS package, problem is its giving me error as below:

Error: 0xC0202055 at Data Flow Task 1, Source - ProviderDetails_csv [61]: The column delimiter for column "Column 18" was not found.
Error: 0xC0202092 at Data Flow Task 1, Source - ProviderDetails_csv [61]: An error occurred while processing file "C:\xxxxxxx\OPS\ProviderODS-ScriptsAndData\ProviderDetails.csv" on data row 542006.
Error: 0xC0047038 at Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Source - ProviderDetails_csv returned error code 0xC0202092.  The component returned a failure code when the

But when I trying to import using

if not exists (select * from sysobjects where name='Import_21Columns' and xtype='U')
    CREATE TABLE Import_21Columns(
    [Column 0] <a href="max">varchar</a> NULL,
    [Column 1] <a href="max">varchar</a> NULL,
    [Column 2] <a href="max">varchar</a> NULL,
    [Column 3] <a href="max">varchar</a> NULL,
    [Column 4] <a href="max">varchar</a> NULL,
    [Column 5] <a href="max">varchar</a> NULL,
    [Column 6] <a href="max">varchar</a> NULL,
    [Column 7] <a href="max">varchar</a> NULL,
    [Column 8] <a href="max">varchar</a> NULL,
    [Column 9] <a href="max">varchar</a> NULL,
    [Column 10] <a href="max">varchar</a> NULL,
    [Column 11] <a href="max">varchar</a> NULL,
    [Column 12] <a href="max">varchar</a> NULL,
    [Column 13] <a href="max">varchar</a> NULL,
    [Column 14] <a href="max">varchar</a> NULL,
    [Column 15] <a href="max">varchar</a> NULL,
    [Column 16] <a href="max">varchar</a> NULL,
    [Column 17] <a href="max">varchar</a> NULL,
    [Column 18] <a href="max">varchar</a> NULL,
    [Column 19] <a href="max">varchar</a> NULL,
    [Column 20] <a href="max">varchar</a> NULL 
) 

if not exists (select * from sysobjects where name='Import_18Columns' and xtype='U')
    CREATE TABLE [Import_18Columns](
    [Column 0] <a href="max">varchar</a> NULL,
    [Column 1] <a href="max">varchar</a> NULL,
    [Column 2] <a href="max">varchar</a> NULL,
    [Column 3] <a href="max">varchar</a> NULL,
    [Column 4] <a href="max">varchar</a> NULL,
    [Column 5] <a href="max">varchar</a> NULL,
    [Column 6] <a href="max">varchar</a> NULL,
    [Column 7] <a href="max">varchar</a> NULL,
    [Column 8] <a href="max">varchar</a> NULL,
    [Column 9] <a href="max">varchar</a> NULL,
    [Column 10] <a href="max">varchar</a> NULL,
    [Column 11] <a href="max">varchar</a> NULL,
    [Column 12] <a href="max">varchar</a> NULL,
    [Column 13] <a href="max">varchar</a> NULL,
    [Column 14] <a href="max">varchar</a> NULL,
    [Column 15] <a href="max">varchar</a> NULL,
    [Column 16] <a href="max">varchar</a> NULL,
    [Column 17] <a href="max">varchar</a> NULL,
    [Column 18] <a href="max">varchar</a> NULL
) 

if not exists (select * from sysobjects where name='Import_4Columns' and xtype='U')
    create table Import_4Columns (
        [Column 0] varchar(max) null
        , [Column 1] varchar(max) null<br />
        , [Column 2] varchar(max) null
        , [Column 3] varchar(max) null
)

if not exists (select * from sysobjects where name='Import_2Columns' and xtype='U')
    create table Import_2Columns (
        [Column 0] varchar(max) null
        , [Column 1] varchar(max) null<br />
)

truncate table Import_21Columns
SET QUOTED_IDENTIFIER OFF
BULK INSERT Import_21Columns FROM 'D:\Users\Abdul\Provider.csv' WITH (FIELDTERMINATOR = '","', ROWTERMINATOR = '\n') 

with FIELDTERMINATOR = ',' I tried both the FIELDTERMINATOR values

Its putting couple of new rows into one single column which is not an expected behavior, the openrowset is giving me linked server error means the dbas are not allowing developers to run certain sql commands. any help please.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

writing complex queries in Sql

emilyz788-Apr-18 8:42

emilyz78

8-Apr-18 8:42

Hello every one,

I need to improve my SQL skills as much as I can Smile | :)

I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck Confused | :confused:

Any ideas?recommendations?
thanx a lot

Emily

writing complex queries in Sql

emilyz788-Apr-18 8:42

emilyz78

8-Apr-18 8:42

Hello every one,

I need to improve my SQL skills as much as I can Smile | :)

I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck Confused | :confused:

Any ideas?recommendations?
thanx a lot

Emily

Re: writing complex queries in Sql

Mycroft Holmes8-Apr-18 12:57

Mycroft Holmes

8-Apr-18 12:57

Presumably you are using a dedicated IDE, SQL Server Management Studio SSMS, or one dedicated to your database.

I would recommend SQL Server Central. Microsoft SQL Server tutorials, training & forum.[^] as an excellent resource dedicated to SQL Server, concentrate on TSQL rather than the DBA stuff. Also try researching books on writing SQL.

Never underestimate the power of human stupidity
RAH

Re: writing complex queries in Sql

CHill608-Apr-18 23:33

CHill60

8-Apr-18 23:33

In addition to the suggestion from @Mycroft-Holmes (which I agree with by the way), don't forget that there are lots of articles here on Code Project that can help you improve on specific topics within the "SQL World" ... SQL articles on CodeProject[^]
Use the star-rating system to get a view on how "good" the rest of the membership think the article is

Re: writing complex queries in Sql

Eddy Vluggen9-Apr-18 2:16

Eddy Vluggen

9-Apr-18 2:16

SQL Quiz (W3Schools)[^]

SQL Online Test | TestDome.com[^]

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Configuring multiple properties or values in SSIS Package using Package Configuration file

indian1432-Apr-18 14:18

indian143

2-Apr-18 14:18

hi all,

I have few SSIS Packages that have ConnectionStrings configured within Package Config file, now I want to set multiple variables values using Package configuration file, but the package configuration file is taking only one variable value, do I need to create different Package Configuration file for each variable that I want to set or is there any way I can set all the variable values using Package Configuration file.

And one more thing is I am going to deploy the SSIS Packages on the Server, where I need to put the Package Configuration Files at different locations, how can I change the Package Configuration Files Path? I am using SSDT 2012 and VS 2012, is it a problem?

Please I need this help, any help would be grateful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 3-Apr-18 16:40pm.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.