SQL 2012 :: Parameterized Sort On Multiple Keys Possible?
Oct 13, 2014
I have sp that works with GUI and have an opton for sort field/ collation. But I noteiced that if I do level on very generic key like <Level> in my case it does the job, but output does'nt look right after that all names, account numbers are messed, so I'd like to add second sort column and looks like it can'be be done with my syntax, I tried to play adding second column and failed.
Select * from T1 order by Level, FirstName
Looks like it only can be done with Dynamic...
DECLARE @SortColmn VARCHAR(10) = 'Level'
SELECT *
FROM TABLE
CASE WHEN @SortColmn = 'FirstName' AND @SortDir = 0 THEN FirstName END DESC
,CASE WHEN @SortColmn = 'LastName' AND @SortDir = 0 THEN LastName END DESC
,CASE WHEN @SortColmn = 'Region ' AND @SortDir = 0 THEN Region END DESC
,CASE WHEN @SortColmn = 'Level' AND @SortDir = 0 THEN [Level] END desc --<@>>< THEN FIrstName asc ???
From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2. Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause. @p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401. if object_id('tempdb..#tbl') is not null drop table #tbl; create table #tbl ( [No] nvarchar(20) ,[Description1] nvarchar(250)
but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
In my project source is Oracle and I am using ODBC to connect oracle for lading.I have create 2 project parameter for connection string one for connection and another for password..when I am making expression on ODBC connection it is showing error like below I can't establish a connection because our legacy driver doesn't support 'Password' as a connection string attribute.
when I am passing expression like @[$Package::V_Constring]+ "PWD=faster1" on odbc connection it working fine.
When I use just the ConnectionString property on the ODBC connection manager and use a 'pwd' attribute; all is well. E.g., "uid=<user>;pwd=<password>;Dsn=<dsn name>;". But as soon as I flip the sensitive attribute, I'm getting the classic error:
The expression will not be evaluated because it contains sensitive parameter variable..The sensitive parameter is desired, of course. I don't want the password in the clear.
I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = “**************string was correct****************� cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser
What are the possible issues I could run in to having multiple foreign keys in a table. Here is why I ask. I have a db (sql server) that has a participant table, a forum table, and a forum reply table. Every record in the forum reply table is associated with the forum table via a PK-FK relationship w/cascading updates/deletes. The participants who post in these tables are not tied back to the participant table via a PK-FK relationship w/cascading updates/deletes. Should they be?
The problem I ran in to is that one particpant was deleted from the participant table but a post with their partid still existed in the forum or forum reply tables.
My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right? If I am right, what do I have to be careful of if I do that?
I am constructing a db in sql server 2000 that will score cross-country running meets. I have an individual results table that needs to only contain participants that are entered as participants but are specific to a certain race as well. Can I have this table be linked back to TWO other tables via the PK-FK relationship and what issues might I have doing that?
I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.
What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?
Can someone tell me what is the best way to check 2 tables lets say tb1 and tb2, to find those customers that are in tb1 but not in tb2 using four fields(cardno,spend,date,refno) as the primary key. Duplicates fields are in tb1
Hi, from the "best practice" perspective, how convenient is to use multiple field primary keys in a table, how convenient is it to replace a multifield pk with an autoincrement field to represent such a table? I tried using a calculated field product of appending the fields that cvompose the primary key, but MySQL for example, does not provide with that functionality, which makes me wonder it doing so in SQL Server is a good practice at all.
Hi Everyone,I am creating a portal and want the user to be able to select four variants from four separate drop down boxes... Such as chapter, story, etc... The user will then be able to click Find and shorten up the gridview list. The below query is what I am using for my current gridview (with custom paging). What I want to do is something like this: Pass a variable such as @Story. If no Story is chosen, then the variable would be *. If a variable is chosen, the the @Story would the StoryID. However, I don't believe SQL recognizes the * in this case. I was thinking it should, but I don't believe it does. I was hoping it would just take it and I could write, SELECT * from Stories where StoryId=@Story, or something like that... and if * was @Story, then it would just select all... or if it was 1, then just stories with a StoryId of 1.Am I totally off base here? Thanks! CREATE PROCEDURE SortAllStories@startRowIndex int,@maximumRows int,@totalRows int OUTPUTASDECLARE @first_id int, @startRow intSET @startRowIndex = (@startRowIndex - 1) * @maximumRowsIF @startRowIndex = 0SET @startRowIndex = 1SET ROWCOUNT @startRowIndexSELECT @first_id = StoriesID FROM Storie ORDER BY StoriesIdPRINT @first_idSET ROWCOUNT @maximumRowsSELECT Stories.StoryId, Chapters.ChapterName, Chapters.EnglishName, Translations.Translation,Stories.Verse, Stories.StoryFROM Chapters, Stories, TranslationsWHERE Chapters.ChapterId=Stories.ChapterId AND Translations.TranslationId = Stories.TranslationId AND Stories.StoryId >= @first_idORDER BY Stories.StoryIdSET ROWCOUNT 0SELECT @totalRows = COUNT(StoryId) FROM Stories
Hi All,I'm trying to solve this for a few days now and I just can't figure itout...I have three tables set up, I'll simplify them for this question:Table 1: HOTELSColumns: HOTEL_ID, HOTEL_NAMEExmple row: 123 || 'Hotel X'Table 2: SERVICESColumns: SERVICE_ID, SERVICE_NAMEExample rows:1 || 'Breakfast in bed'2 || 'King size bed'Table 3: LINK_HOTELS_SERVICESColumns: FK_HOTEL_ID, FK_SERVICE_ID, SERVICE_VALUEExample rows:123 || 1 || 1123 || 2 || 1In table 3 I link different services to different hotels. In the same tableI set the "value" for the service.The first example row of table 3 means something like: Hotel X offersBreakfast in bed. In this case 1 stands for TRUEThe second example row of table 3 means: Hotel X offers King size beds(again: 1 stands for TRUE).What I'm struggling with is selecting the hotel ID's which offer multipleservices. To stay in the example: how can I select all hotels whereSERVICE_ID = 1 AND SERVICE_ID = 1. I can't seem to figure out how to doit...I hope anyone can help... Thanks a lot in advance!!!Robert
I have a table which has 10 columns which make up the secondary key. 1or more of these columns can be set but the remaining columns in thesecondary key will be null. For example :id k1 k2 k3 k4 k5 k6 k7 k8 k9 k10 data-------------------------------------------------0 1 1 - - - - - - - - test01 1 1 1 - - - - - - - test12 1 1 2 - - - - - - - test23 1 1 3 - - - - - - - test34 1 1 3 1 - - - - - - test45 1 2 1 - - - - - - - test56 1 2 2 - - - - - - - test6Each row represents a node in a tree structure, the secondary keycolumns point to which node in the tree. The above example has onenode in tree branch 1, three nodes in tree branch 1-1, one node inbranch 1-1-3 and two nodes in branch 1-2.My question is how can I write a single stored procedure to returnonly the nodes in a given tree branch without needing logic based onlooking for NULL parameters. I have written the stored procedurebelow. If I want to retrieve all nodes in branch 1-1 (ie; test1, test2and test3) then I want be able to call :execute "mysp Test" 1, 1But this only returns a single record, test0. The reason for this isobvious if you look at the stored procedure. What I really need is wayof sayingexecute "mysp Test" 1, 1, NOT NULLso that it returns all records in branch 1-1 where Key3 is NOT NULLbut all subsequent keys are null, ie; Key4-Key10 are NULL. The storedprocedure must work with any number of secondary keys though, so Icould use it to call :execute "mysp Test" 1, 1, 3, NOT NULLwhich would retrieve test4.Can anybody help me out here?Thanks for reading.Here's the stored procedure as it stands :CREATE PROCEDURE [dbo].[mysp Test]( @key1 smallint,@key2 smallint=NULL,@key3 smallint=NULL,@key4 smallint=NULL,@key5 smallint=NULL,@key6 smallint=NULL,@key7 smallint=NULL,@key8 smallint=NULL,@key9 smallint=NULL,@key10 smallint=NULL ) ASselect * from TreePositionTestwherekey1=@key1 andkey2=@key2 andkey3=@key3 andkey4=@key4 andkey5=@key5 andkey6=@key6 andkey7=@key7 andkey8=@key8 andkey9=@key9 andkey10=@key10GO
Hey,I am having some confusion about how to formulate this particularquery.I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with thecolumns a1,a2,a4 forming the primary key. Table B again has 3 columnswith b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.All columns are of the same datatype in both tables. Now I want to getrows from table A which are not present in table B. Whats the best wayof doing this?Thanks--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict235166.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725
I want to partition this table on 5 different coulumns.
year, month. coulumn3, coulumn4 and coulumn5.
How do I do this best?
What is the best way of doing this; is there any best practice on ths?
The obvious thing is to make a partition funktion based on the year and month coulmns, but I also want to partition on coulumn3, coulumn4 and coulumn5.
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
Probably not the best "Subject Title", but no the less what I am looking for is a way to get the order in which I should insert data into a table that has 10 FK's to it. I have queries that list them out for me, but they do that in what seems to be alphabetical order. You can also right click the table in question and select view dependencies, but that as well appears to be in alphabetical order.
Is there any query or any way to see what order I should be inserting and/or deleting data into FK tables?
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
I am trying to execute following sql script in sql-server 2000 query analyzer
CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY, course_id varchar(10) NOT NULL PRIMARY KEY)
Its give's me following error :- Cannot specify multiple primary key constraint
Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.
Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys. Will it allow me to have following records in the user_courses table? user_id(P.K) course_id(P.K) bob CRS235 alice CRS235 Tim CRS235 tom CRS635
So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?
I have a database in XML format , with 100+ tables, that I want to import into SQL server but the problem is that I don't have any of the original primary key information. I know that some of the tables had multiple primary keys.
Is there a method or a tool that is capable of determining the primary keys for me??
I don't want to have to figure it out by hand, especially if I have more of these databases in the future.
I'm trying to create a DM model using TS algorithm, to predict sales for different products and channels but I can only get it to work using one of those two "dimensions" or columns the other one is ignored (This is, my fact table contains a key for time, a key for channel a key for product and the metrics and the model only seems to allow working with time, the metrics and only one of the other dimensions product or channel ..) Am I missing something?
I have a table called Appointment, for storing (you guessed it) appointments at a medical practice. If an appointment is cancelled, I want to collect a cancellation reason, so let's say that I create a second table called Cancellation which has a foreign key relating to the Appointment table's primary key, AppointmentID, and another column, Reason. Now, in order to indicate that an appointment was cancelled, I could include a Cancelled column in the Appointment table with a bit datatype, or instead I could infer that an appointment must be cancelled if it has a corresponding record in the Cancellation table.
It may be that it'd be better to store the cancellation reason in the Appointment table - But regardless, let's say I stick with the two-table solution described above, and I subsequently want to write a query to list all appointments which have been cancelled. If I had the Cancelled column in the Appointment table, I could simply query for all records in that table where that column's value was FALSE. If I went the other way and DIDN'T have a Cancelled column, I could instead write a query joining the Appointment and Cancellation tables to return all records in Appointment with a corresponding record in Cancellation.
That latter method, whilst slightly more complicated because it involves joining two tables, seems to me to be the most normalised. Instead of storing the fact that an appointment is cancelled in two different tables, that fact is only stored in the Cancellation table. Would there be a performance hit in using the two-table, 'inferred cancellation' method rather than just having a bit column in the Appointment table? Would that performance hit be enough to persuade you to use a Cancellation column in the Appointment table instead? And what about if I were to apply that method to other things associated with each appointment, e.g. Is it completed? Is it chargeable to the client or an insurance company? Is the client and in-patient or out-patient?
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
I have question about the size of foreign key’s in sql-server 2012. If I in one table had a foreign key of the “INT” type. Do it still cost 4 bytes of storage?
We have a database where the nvarchar columns currently holding English only data. It keeps the training information. Currently the default collation is Latin Case insensitive accent insensitive.
Now we are planning to allow multiple language support. When we go for it, we will upload the data from different languages. Based on user preferences, he/she should be able to query the data (One language at a time).
I am worried on how the existing queries will work if I load all language data in the same database.
For example, some characters in English are used in Norwegian too. But they have different sort order. ALso LIKE conditions too may fail.
So far I thought of few solutions: Add the Collation information along with select , order by clauses. It means we need to add more procedures (one set per collation) The other option is to create new database for each language. Each will have its own collation. The dowside is we need more databases which may lead to more servers and more maintenance work.
I'm new to using SQL Server. I've been asked to optimize a series of scripts that queries over 4 millions records. I've managed to add indexes and remove a cursor, which increased performance. Now when I run the execution plan, the only query that cost is a DELETE statement from the main table. It shows a SORT which cost 71%. The table has 2 columns and a unique index. Here is the current index:
ALTER TABLE [dbo].[Qry] ADD CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED ( [QryNum] ASC, [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Question: Will the SORT affect the overall performance? If so, is there anything I should change within the index that would speed up my query?
I am developing a BI solution on SQL Server 2008 R2 and how to handle multiple referances to the same dimension from a fact table!
Here is the scenario;
Fact_Contracts (# M) ServiceProvider_CompanyID, Client_CompanyID, Amount_USD Dim_Company( hundreds) ID, CityID, ProfessionID, CompanyName Dim_City ID, CityName Dim_Profession  ID, ProfessionName
As u can see there is two company references in my fact table, and the schema is in snowflake. My customer requirements state that the Contracts' amounts can be aggregated/filtered for/by, ServiceProviderCompany, its city/profession or ClientCompay, its city/profession.
First thing came in to my mind is to dublicate whole dimension structure (one for serviceproviders, one for clients), which i thought that there should be another way around?
I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.
The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
