Sequence Of Columns And Preformance
Jul 6, 2006Hello,
I am learning SQL Server and I would like to ask you a question. Could
you tell me please if sequence of columns in a table has influence on
performance?
Thank you very much
/RAM/
ADVERTISEMENT
Sequence Of Columns In Primary Key
Oct 17, 2005I am currently undertaking a review of the primary keys in a SQL Server2000 database with a view to improving performance of queries.I have heard that, in the case of compound primary keys, it isimportant to select the correct order for the columns within the key.For instance, imagine a table called OrderLine which has primary keycolumns as follows-LedgerOrderNumberOrderLineNumberThe theory I have heard is that columns with the most distinct valuesshould come first. In this case, Ledger is likely to have a maximum of6 distinct values, OrderNumber a maximum of 10 million and OrderLine upto 99. Based on this supposition I believe the best order would be-OrderNumberOrderLineNumberLedgerI have performed a few rudimentary tests that appear to bear this out.I was wondering if anyone else has tried something similar and if sowhat was the result?Thanks,Ross
View 14 Replies View RelatedDynamically Concatenating Multiple Columns In Sequence?
Oct 16, 2014I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths.
eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
---------
Table BSadIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
---------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN--> 10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif
then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
Updating Two Columns With Sequence Numbers With Where Clause?
Jun 29, 2015I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
my example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
SQL Server 2014 :: Dynamically Concatenating Multiple Columns In A Sequence?
Oct 16, 2014I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths. eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
Table BIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR--> 1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
Data Mining :: Updating Two Columns With Sequence Numbers With Where Clause
Jun 29, 2015I have a question in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
Preformance And Hardware Needs
Mar 6, 2001Does anyone have any formulas or standards that you use in determining the hardware needs of either a new server or for keeping your current server in top running condition.
Also, I know some of the basics for performance on the databases, but any ideas on things I should look for (or documentation links) on utilizing the best performance of the server itself?
thanks in advance for any comments
Upgrading MY Preformance
Jul 20, 2005Hello all! I'm a newly appointed database administrator and have beenstruggling with my consious about what would make me an excellentdatabase administrator. I've worked in Access for years and know thatif your comfortable with:1. writing expressions2. writing some VBA3. the basics with Tables, Forms, Queries and Reports.and you'd be sitting pretty. SQL Server is a little more robust thanAccess and i'm not sure where I should start.I'm comfortable with writing T-SQL statments, backing up, and creatingusers. but other than that, i'm a little lost on what else i shouldknow.thanks.
View 1 Replies View RelatedSQL ServerAgent Preformance Degridation
Dec 22, 1999I have found a huge prefomance degridation running something thru a Job on the server and running it through ISQL.
I have a sp the runs in about 24 min thru ISQL, but when I fire it off thru a job at night using the Agent, it take roughly 4.5 hours... Way slow...
Any thoughts?
Need To Know The Preformance Statistics Of SSIS!
Nov 6, 2007Hi All,
Need to know the preformance statistics of SSIS!
Please reply Ur comments,
Regards,
Sandesh
Poor Preformance Usinng Sql 2005 With Access 2003 Application
Jan 1, 2007we have run access 2003 application using SQL 2005 server
and the data transfer rate is extreamly slow.
we are using ODBC connection
and the access apllication works very fast with SQL 2000.
we have the tested the comptabilty of the application to SQL 2005 with Microsoft wizard and it had no problems.
even when i run simple application on the sql 2005 server, it runs very slowly.
i am using the MDAC clients ODBC component and the SQL Native Clients one.
which eliminates the possibilty of network problem.
what could be the problem???
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Dec 13, 2007Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Sequence No.
Feb 6, 2004Hi,
How can I generate a sequence No. using a simple SELECT statement.
like
declare @key
set @key = 1
SELECT @key, e.name from Employee
Now I want to display name of the employee and Key value which should get incremented automatically for each employee..
Is there any way?
Please help me..
Sql Sequence
Dec 18, 2007hi
i've been asked to write a sql sequence for a database i'm building but i haven't been using SQL very long and i have no idea how to write a sequence. Does anyone know anything about sql sequences?
thanks
jessica
Sequence
Jul 20, 2005Hi,I need little help with Ms SQL Server 2000. I would like to know how tocreate sequence or something like that. I want to have an automatic counterfor each row in one entity, so then I can do something like this:INSERT INTO table VALUES (use sequence(something), value, value, .... )Can you please help me?thxTomas
View 1 Replies View RelatedHow To Add A Newline Sequence
Apr 9, 2007I have a SQL 2005 stored procedure to generate an email when passed parameters such as receipient, subject etc
One of the paramteres passed to it is @body which is the body text of the message. I want to be able to add a couple of blank lines and then some footer information. This is working right now except I can't find the right way to add newlines into the string within the store procedure, so my footer information just tags right on after the bodytext.
I have tried but that literally adds the two characters and n
Can anyone advise how to generate newlien sequences in T-SQL.
Regards
Clive
Sequence Order
Apr 27, 2008Hi all.
In one table fields vales are 9, 5, 7, 2, 5, 6, 44, 67, 789.
I want to get 5 high values so please can you write sql query for this question.
Thanks.
Zahyea.
Log Sequence Number
Apr 16, 2002Hi, I've a question, if I've the LSN (Log Sequence Number) of a transaction, keep with the program "Log Explorer", can I know which is the IP of the user that have do the transaction (perhaps serching in a log file of the Win 2000 Server) ?
10x,
Clara
Sequence Number
Dec 29, 1998In SQL 6.5 object dependencies window, what does the sequence number means?
Thanks,
Sam
How Can I Create A Sequence ?
Jul 16, 2001Hi,
I would like to know how it is possible to create a sequence with sql server 2000
With Postgres SQL i create the sequence essai : create sequence essai START 1;
but i don't know what is the sql command.
Thanks in advance.
Marie
Escape Sequence
Sep 1, 2004Hi,
I have a question with this query -
SELECT * FROM table1 WHERE column1 = 'T_C_%';
This query returns rows where column1 = "T_Care", "T_CRP" etc etc, whereas I was expecting only rows where column1 = "T_C_Tail", "T_C_Head"
However, when I use an escape character(/), the results are more in the lines of the expected results.
Can somebody explain this?
cheers/- Pradeep
Sequence Number Help
Nov 17, 2005I have the following stored procedure:
CREATE PROCEDURE dbo.ABR_HDR_INSERT
@id int output,
@status int,
@mode int,
@sessid varchar(100)
AS
declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int
SELECT @ddo = a.DDO_DSCR_SHORT
FROM dbo.DIM_DDO a
JOIN dbo.Temp_ABR_HDR b
ON a.DDO_ID = b.DDO
WHERE b.SESSIONID = @sessid
SELECT @ay = AY
FROM dbo.Temp_ABR_HDR
WHERE SESSIONID = @sessid
-- set the default seq_no
SELECT @seq_no = 1
-- get the max abrid. if no record return the seq_no will be 1
SELECT @seq_no = convert(integer, max(right(abrid, 4)))
FROM dbo.ABR_HDR
WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0
SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4), @seq_no)), 4)
Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)
SELECT
@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status
FROM dbo.Temp_ABR_HDR
SELECT @id = @@identity
return @id
GO
ABRID gets inserted as a <NULL> value. I can't figure out why? If I comment out the following then ABRID will insert without the sequence number:
CREATE PROCEDURE dbo.ABR_HDR_INSERT
@id int output,
@status int,
@mode int,
@sessid varchar(100)
AS
declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int
SELECT @ddo = a.DDO_DSCR_SHORT
FROM dbo.DIM_DDO a
JOIN dbo.Temp_ABR_HDR b
ON a.DDO_ID = b.DDO
WHERE b.SESSIONID = @sessid
SELECT @ay = AY
FROM dbo.Temp_ABR_HDR
WHERE SESSIONID = @sessid
-- set the default seq_no
--SELECT @seq_no = 1
-- get the max abrid. if no record return the seq_no will be 1
--SELECT @seq_no = convert(integer, max(right(abrid, 4)))
--FROM dbo.ABR_HDR
--WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0
--SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4),@seq_no)), 4)
SELECT @abrid = @ay + UPPER(@ddo)
Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)
SELECT
@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status
FROM dbo.Temp_ABR_HDR
SELECT @id = @@identity
return @id
GO
So, the code that sets the sequence number is what is causing the <NULL> value.
Any help is appreciated.
Thanks,
-D-
Sequence Tables
Feb 1, 2007Hi,
I am migrating a project from Oracle to SQLServer and must use the same DDL.
The entities have compound primary keys. In Oracle I ensure the compound key is unique by using a sequence table to generate one of the values for the compound key.
Does SQL Server have sequence tabels or does anybody know a way to get it do something similar?
Thanks
Max Value For Sequence Table
Apr 27, 2007Hi,
How do I determine the max value for Sequence table in SQL Server?
Regards
Sequence Number
Oct 3, 2007suppose I have the following table grouped by memid
memiddx
3455
3322
3232
433
43434
I want to attach sequence number for each unique value of dx per memid as
below
memiddxSEQ
34551
33222
32323
4331
434342
I am using a cursor right now and it takes a lot of time if my table is large.
Is there a more efficient way of doing this.
Thanks much.
Number Sequence
Jan 11, 2008is it possible to generate a number sequence in a query (without using loop). I want the output to look as
-------
ID
-------
1
2
3
4
5
6
7
8
9
....
upto the last number I give in the query
Sequence In Rollback
Oct 16, 2014I am not sure about the ANSI/ISO rules on this? We have a SEQUENCE and insert it into a table.
CREATE SEQUENCE Cheque_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1;
CREATE TABLE Cheque_Book
(cheque_seq INTEGER*NOT NULL PRIMARY KEY,
cheque_amt DECIMAL (15,2) DEFAULT 0.00 NOT NULL);
If I do an insertion with the SEQUENCE in a transaction and then ROLLBACK the transaction, what happens to the sequence value? My thought is that the value should reset to the state it was in before the transaction (basic definition of a ROLLBACK). But it does not in the products I have looked at.
Sequence And Triggers
Apr 4, 2008Hi SqlGurus,
I have created table T1 as
created table t1 (no int not null primary key,name varchar(30))
i have created a sequence for this table
when i give the following insert statement
insert into t1 (name) values ('xyz')
the next sequence val should be inserted in column no
this is done generally in oracle with before trigger
how about here .Can we do this ?
Sequence From A Function
Mar 10, 2006Hi
I need to create a function that will return a sequence number. The reason I need this to be a function is that I will use the function to create a view. In Oracle I would do something like this:
create sequence seq;
create or replace function f return number
as
l_seq number;
begin
select seq.nextval into l_seq from dual;
return l_seq;
end; /
create or replace view v as select f from dual;
Since Sql Server does not have a sequence object I have created a sequence table with a identity column and a procedure that inserts a dummy variable and returns the identity id. This works ok but to get my sequence number I must declare a variable, execute the procedure and select the return value.
I need the procedure to be a function so that I can use it to create my view. Since I cannot use DML in a function and I cannot call a procedure from a function I am stuck. Do I have to create an extended procedure ? Any help is appreciated
Sort Sequence
Mar 28, 2007hey all,
im having some problem to do this. i have this one table, that has sequence for other table.
table : tblpicksequence (this sequence is dynamic eg : Non=1, cons=2, RET=3)
picktypepicksequenceitemref
RET1x1
Non2x1
Cons3x1
i need to select record from other table depend on tblpicksequence sequence
table : tbldetail
idRETNonCons
1001
2110
3100
4 001
so base on tblpicksequence, RET=1, NOn=2, Cons=3, i need to list the record from tbldetail :-
i.list all RET=1
ii.if (i) not exists list all Non=1
iii.if (ii) not exists list all Cons=3
i expect :-
idRETNonConspicksequencepicktype
21101RET
31001RET
~~~Focus on problem, not solution~~~
Sequence Counter
Nov 5, 2007I'm looking for a query that will look at an Id field and if it occurs more than once then returns the count of the times it occurs. For Example,
ID Code GetSequence
4 239 1
4 241 2
4 3243 3
Sequence Of Execution
Dec 26, 2007Hello to every body
I need to know about sequence of execution in a select command.
I have a sql command that use a function.some thing like:
select id, function(item)
from tbl
where conditions...
I want to know that if my function on item execute before where section or vise versa.
I try to explain it more. I want to know that sql engine fetch rows accordin to where clause and then execute my function or execute my funtion and then fetch the rows according to where clause.
If you have a document or some thing that explain about sql engine and sequence of execution please let me know.
Your help is really appriciated.
ORDER IN Particular Sequence
Jan 23, 2008Hi,
I have a table in which there is 5 column ...one with numbers like...1,2,3,4,..20...and one column is with description....and other column with other details...
Now I want to disply my results in sequence followed by 1,2,3,4,5,17,18,15,16,10,11,20 with all other columns...so can anybody suggest me what to do..?