|
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. 
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.
|
|
|
|
|
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. 
Turns out that all my questions may be moot (aside from my own learning) 
For review exec sp_helpindex <table name> gives the following description
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!
|
|
|
|
|
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.
|
|
|
|
|
One has twice the indices... well duh.
I should have thought of that... but I didn't 
Thanks for sticking with me until I got it!
|
|
|
|
|
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.
|
|
|
|
|
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 
Bastard Programmer from Hell 
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.
|
|
|
|
|
Thanks Eddy.
modified 20-May-21 21:01pm.
|
|
|
|
|
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.
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
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 +'%'
order by act_finish_time_local desc
)
as j on j.act_finish_time_local < e.startDT
how can i fix this?
Thanks
|
|
|
|
|
|
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
|
|
|
|
|
|
Try using CROSS APPLY instead:
Making OUTER and CROSS APPLY work for you[^]
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
|
|
|
|
|
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."
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
|
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."
|
|
|
|
|
Hello every one,
I need to improve my SQL skills as much as I can 
I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck 
Any ideas?recommendations?
thanx a lot
Emily
|
|
|
|
|
Hello every one,
I need to improve my SQL skills as much as I can
I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck
Any ideas?recommendations?
thanx a lot
Emily
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
|
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.
|
|
|
|
|
Hi all,
I am trying to insert values into Table variable within Dynamic Sql, I am able to select values from Table variable but not able to insert values in to the Table variable.
Here is how I am selecting values from Table variable
CREATE TYPE IntegerTableType AS TABLE (ID INT);
go
DECLARE @TempVehicles IntegerTableType;
INSERT @TempVehicles
values (1);
DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT *
FROM @TempVehicles;';
EXECUTE SP_EXECUTESQL @SQL,N'@TempVehicles IntegerTableType READONLY',
@TempVehicles;
IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='IntegerTableType')
DROP TYPE [dbo].IntegerTableType;
GO
But when I am trying to insert values as below, I am not able to insert.
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='Results')
CREATE TYPE Results AS TABLE (TableName nvarchar(500), ColumnName nvarchar(500))
GO
Declare @Tables table (Id int identity(1,1), TableName nvarchar(500))
declare @Columns table (Id int identity(1,1), ColumnName nvarchar(500))
declare @Results AS Results
declare @IdMin1 int, @IdMax1 int, @IdMin2 int, @IdMax2 int, @col varchar(500), @cmd varchar(max), @TableName nvarchar(500)
insert into @Tables(TableName)
SELECT t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo'
order by t.name
select @IdMin1=min(Id), @IdMax1=max(Id) from @Tables
while(@IdMin1<=@IdMax1)
begin
set @TableName = (select top 1 TableName from @Tables where Id = @IdMin1)
<pre>
delete from @Columns
insert into @Columns(ColumnName)
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName
order by c.name
--insert into @Results (TableName) values(@TableName)
select @IdMin2=min(Id), @IdMax2=max(Id) from @Columns
while(@IdMin2<=@IdMax2)
begin
set @col=''
select @col=ColumnName from @Columns where Id=@IdMin2
SELECT @cmd = 'IF EXISTS (SELECT top 1 * FROM ' + @TableName + ' WHERE [' + @col +
'] IS NULL) BEGIN insert into @Results (TableName, ColumnName) values( ''' + @TableName +''',''' + @col + ','') end';
begin try
--select @cmd
--EXEC(@cmd)
EXECUTE SP_EXECUTESQL @cmd, N'@Results Results READONLY', @Results;
end try
begin catch
SELECT @col=@col + ', Error Message: ' + ERROR_MESSAGE()
end catch
--insert into #Results (ColumnName) values(@col)
set @IdMin2+=1
end
set @IdMin1+=1
end
select * from @Results where TableName is not null order by TableName, ColumnName
IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='Results')
DROP TYPE [dbo].Results;
GO
Can anybody please help me how can I finish this and let me know why is it not inserting into Table variable using Dynamic Sql, is there any way to perform that action. Any help would be much helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Is there a reason why you are creating a user defined TYPE for each table? Why not create a table variable
Declare @Tbl TABLE(ID int)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
