Tag Info

New answers tagged

1

lat and lng are obviously numbers, so you should store them as appropriate numeric data type, not as text. That makes for smaller storage and disallows invalid input, it simplifies your query syntax and is also a bit faster overall. It's not going to do much for your query, though. For instance: lat | numeric lng | numeric So ...


2

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist. If you haven't already, run the restore command with the verify only option MSDN. If that is successful here are a few additional things you can try to isolate why it is failing If you have the space on the ...


4

The clause after on is the condition which is if met, the row will be returned with the result set. In your example, the condition is always met, so the result is the Cartesian product of the joined tables. This is similar to saying ON 1=1, which is always true. You will get the same result if you don't add the condition with the inner join.


1

Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination? This is one of the Myths that Paul Randal clearly debunks in his famous series - A SQL Server DBA myth a day On 64-bit systems (2005+): AWE is not required (and in fact enabling it does nothing). Also refer to Fun with Locked Pages, AWE, Task ...


0

The tool I needed to figure it out was RAMMAP, which showed the AWE pool as the memory hog. Yes, someone enabled AWE on a 64-bit OS with 64-bit SQL.


0

You have a table that contains All (Active and Inactive) Doctors and you have a file that contains Active Doctors. The question then becomes how can you determine who isn't in the file? The SSIS way to do this is to use the Lookup Task, just as you are already doing but instead, you're going to use a Cache Connection Manager to allow you to use the flat ...


0

Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well: This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source ...


2

I think this could be a whitespaces issue. The problem is that MySQL ignores trailing whitespace when doing string comparison. See http://dev.mysql.com/doc/refman/5.6/en/char.html All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. ... For ...


1

You can easily find the subsequence when you cast the arrays to strings and replace the curly brackets with commas: translate(cast(sequence as varchar(10000)), '{}',',,') {1,3,17,25,377,424,242,1234} -> ',1,3,17,25,377,424,242,1234,' Do the same for the array you're searching for and add a leading and trailing %: '%' || translate(cast(searchedarray ...


0

A semi-solution: They will be in column. (But they are also in a single row, sort of.) SELECT CONCAT_WS("\n", her_name, his_name, other_name) AS name FROM foo For example: mysql> SELECT CONCAT_WS("\n", province) FROM Provinces; +---------------------------+ | CONCAT_WS("\n", province) | +---------------------------+ | Alberta | | ...


0

After further research, all I needed to do was drag the Colors table into the design view TWICE. Then link one to each ID (Top, Bottom) in the outfit table. Then set the alias and done.


3

Basic answers Since you select a couple of big columns (info in comment) an index-only scan are probably not a viable option. This code works (if no NULL values in data!) Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well: SELECT <some ...


1

OK, I think I found a way. This may not be the best way (using the GUI) I create Query for each sub query. On TopQuery: SELECT outfits.TopColorID, Colors.Color FROM Colors INNER JOIN outfits ON Colors.ID = outfits.TopColorID; On BottomQuery: SELECT outfits.BottomColorID, Colors.Color FROM Colors INNER JOIN outfits ON Colors.ID = outfits.BottomColorID; I ...


1

Put this in the SQL View of the query design and you can then see the GUI required to build what you want. select ID , (select colorname from colors where id = outfits.topcolorid) as TopColorName ,(select colorname from colors where id = outfits.bottomcolorId) as BottomColorName from outfits;


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


0

How about using INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO on master itself?


0

Generate a calendar table (that's googleable) and join your other tables to it. This makes it easy to do overlaps, etc. in an efficient way. In case it is not clear this is the standard way. I haven't gone into example usage because it is too involved for an answer here. Search for "calendar table" will give lots of examples and a full explanation to ...


1

I love Aaron Bertrand's answer. Although I don't understand it completely, it looks really elegant. In the past I've ran into problems with permissions when using sys.objects. Combined with the need for me to troubleshoot the code, I've come up with a variation on Aaron's code, and added it below. This is my procedure: CREATE PROCEDURE ...


0

you can also achieve this result by using Pivot as below. ;with cte as ( SELECT sq.survey_cat_id, sd.Store_id,answer FROM survey_details sd INNER JOIN survey_question sq ON sd.ques_id = sq.ques_id ) Select * from cte PIVOT( COUNT(answer) FOR answer IN ([excellent], [very ...


3

It is unclear to me what is a "more elegant way". Oracle you can use the following statement to make columns to rows select all_name from foo unpivot (all_name for col_name in ( his_name, her_name, other_name)); This is the syntax diagram of the select statement SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] ...


0

You may have already attempted this, but I must ask: Have you attempted to initialize mirroring via the SSMS GUI? In SSMS on principle server, right-click datbase --> Mirror --> Configure Security. I'm curious if the same error message is generated from the GUI. Can you establish a connection to your mirror server from the principle server via SSMS?


1

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on ...


0

select x.* from member as x where x.id IN ( select id from member group by id having count(distinct email) > 1 )


1

All three options you are looking at are bound to perform poorly in a concurrent environment. Wrapping SELECT (MAX(wid)) ... in a transaction won't prevent race conditions; you'd need to write-lock the entire table for that. If you don't need the sequence numbers to start from 1 for every branch, as long as the values are increasing and unique, you might ...


0

Well, I think this is a good example when SET operations might not be the best tool in the box. I'd go for a simple cursor here! :) Both easier to read, maintain and scans the table only once! An RDBMS does not really have the concept of 'ordered table' unless you explicitly specify it with an ORDER BY. I do, however, guess your table is 'ordered' by < ...


0

You could setup a history database (using nzhistcreatedb) which acts as a detailed access log and then consult the views and tables that it provides (e.g. $v_hist_table_access_stats). If you haven't already got this in place then I'm not aware of any way to find out who accessed a table unless you trawl through the logs in /nz/kit/log/postgres/.


1

I've modified your query and I used CASE statement to COUNT: Query: select survey_categories.survey_cat_id, survey_question.ques_id, survey_details.store_id, count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent, count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good, count(CASE WHEN ...


1

The only difference between the two queries is that the 2nd will always return 1 row. Even if the table is empty or just has 0 rows with reference=X, the result will be 1 row with null in the min(date) column. The first query will return no row in this case. The and rownum=1 is useless and does not affect anything for the specific query. Test at SQLfiddle. ...


0

If the string is being thrown directly out to a HTML stream then you could do Select 'Your submitting date is 2<sup>nd</sup> January 2016'; but that really isn't recommended practise - you should avoid making presentation layer decisions like that within your data layer. If you tightly couple your back-end to your presentation layer in ways ...


2

Another way would be to use EXCEPT like this: SELECT u.id AS user_id, s.id AS song_id FROM users AS u CROSS JOIN songs AS s EXCEPT SELECT user_id, song_id FROM songs_played ; The result would be all u.id, s.id pairs that are not found in songs_played.


0

Create a script component as a data source. You can probably re-use a lot of your code that you used to create the script task. Then you can set your batch size.


1

Typing from my phone, you could do something like: select x.* from t as x join ( select id from t group by id having count(distinct email) > 1 ) as y on x.id = y.Id You seem to be missing a primary key, but I assume this exercise is part of fixing that.


1

You can query v$sqlarea, which holds sql history for a little longer than v$sql (holds info only about current running sql). Both have similar columns, but depending on the report you want to extract, you might wanna join v$sqlarea with other views. But V$sqlarea is also limited to a period of time, and if you need to go more in depth, the view is ...


0

You could make a start by using sed, awk and grep to change MySQL data types to SQL Server ones - but without a server to test on, you're really flying blind.


1

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.


0

So i assume that you already have the queries to insert the data. So the question is really "Can i also check that i m not inserting same data over and over ?". There are a couple of ways you can do this. One simple way is to create a constraint on the columns in your table. Let's assume that they are col1,col2 and col3. So you could create a unique ...


7

Subtract one from each other in YYYYMMDD format, and divide by 10000 using integer division (which rounds down). So: (YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate) - YEAR(@DateOfBirth) * 10000 - MONTH(@DateOfBirth) * 100 - DAY(@DateOfBirth) ) / 10000 20150725-20000727 = 149998, and 149998 / 10000 = 14 You can also use ...


3

You can use not exists clause to do this. SELECT u. ID AS user_id, s. ID AS song_id FROM songs s, users u WHERE NOT EXISTS ( SELECT 1 FROM user_songs us WHERE us.user_id = u. ID AND us.song_id = s. ID ) ORDER BY 1, 2


1

SELECT MAX(Description) as MAX_Description,a.Structure ,a.Description FROM (select ,CASE WHEN Type = 'Test' THEN Structure ELSE '' as Structure CASE WHEN Type = 'Test' THEN Description ELSE '' END as Description from <Table_Name> ) a GROUP BY a.Structure ,a.Description


3

USING UNION Others have submitted answers trying aggregation to collect data without using UNION May 12, 2014 : Query improvements without UNION May 05, 2015 : Calculating values from three related tables, without using join or union Feb 20, 2012 : SQL Data aggregation In this instance, what makes UNION an absolute must is the merging of three columns ...


2

From Itzik Ben-Gan's Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference): Logical Query Processing Order: 1 FROM 2 WHERE 3 GROUP BY 4 HAVING 5 SELECT 5.1 Evaluate Expressions 5.2 Remove Duplicates 6 ORDER BY 7 OFFSET-FETCH/TOP Window Functions operate in steps 5.1 and 6. Obviously then, you cannot access ...


0

You didn't specify your DBMS, but this is quite easy with Postgres: with product_list as ( select order_id, string_to_array(unnest(string_to_array(products, '|')),':') as prod from orders ) select order_id, prod[1] as product, prod[2]::decimal as price from product_list order by order_id; SQLFiddle: ...


-3

Easy solution would be this: SELECT CONVERT(VARCHAR,her_name) AS name FROM foo UNION ALL SELECT CONVERT(VARCHAR,his_name) AS name FROM foo UNION ALL SELECT CONVERT(VARCHAR,other_name) AS name FROM foo


2

Let me try for a pure SQL solution: SELECT id FROM ( ( SELECT *, 0 AS chck FROM tableX WHERE id > @givenID ORDER BY id LIMIT 3 ) UNION ALL ( SELECT *, 1 FROM tableX WHERE id <= @givenID -- just in case of a small table (< 3 rows) ORDER BY id LIMIT 3 ) ...


2

If you prefer not using stored procedures, here is my way: set @my_id=7; set @rank=0; select id from ( (select id, @rank:=@rank+1 as x from t where id>@my_id order by id) union (select id, @rank:=@rank+1 as x from t where id < @my_id order by id) ) my_table limit 3; You change the value of @my_id accordingly. ...


1

I've made a Stored Procedure where you can send the STARD_ID and the QUANTITY of rows. Example: mysql> SELECT * FROM test.tbl; +----+ | ID | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 7 rows in set (0.00 sec) mysql> CALL test.rows(6,4);; +---------+----------+----------+ | STR | START_ID | QUANTITY | ...


0

What is the need to have two servers with the same database and the same information? The Two databases receive data from system? It would not be the case that you look at the SQL Server Replication? https://msdn.microsoft.com/en-us/library/ms151198(v=sql.105).aspx If it don't fit try implement a trigger that do it for you.


1

Something like this should get you started. The procedure will get the correct rows. It would have been a lot easier if you hadn't wanted to restart at the beginning when you reached the end. :) delimiter $$ create procedure getRows(in inID int) begin declare maxID int; declare IDPlusOne int; declare IDPlusTwo int; declare ...


0

This is a solution for SQL Server: Used this helpful link as a base (http://www.databasejournal.com/features/mssql/converting-comma-separated-value-to-rows-and-vice-versa-in-sql-server.html). Instead of creating the function object, I iterated through. Depending on how large your source table is and how persistent you want the result set to be, you may ...



Top 50 recent answers are included