2
votes
4answers
54 views

Null Or Multiple Parameters When Querying

I have a stored procedure that has a parameter that must take a comma delimited varchar list of values that can be turned into a table of values or can be NULL, such as @Values VARCHAR(10) = NULL ...
31
votes
8answers
7k views

Is select * still a big no-no on SQL Server 2012?

Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit. Is this still the case in later versions of SQL ...
0
votes
2answers
79 views

Multiple SELECT vs single SELECT over TCIP

My question is a performance one: I need to select roughly 400+ rows, i know their ids. currently i loop in code and fire off multiple queries over tcp/ip - because its easy: ... select [columns] ...
1
vote
2answers
136 views

multiple select query for the same tables with union [closed]

Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. ...
4
votes
0answers
214 views

SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key [on hold]

How is the deadlock graph below possible? How did two SELECT statements simultaneously acquire an exclusive lock on the exact same resource? The exclusive locks were on the primary key index for ...
2
votes
1answer
58 views

Select query - group by and sub group by

CREATE TABLE [dbo].[temptable] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [uid] INT NOT NULL, [pid] INT NOT NULL, [event] INT NOT NULL, [timeStamp] DateTime NOT NULL ) I want ...
-2
votes
1answer
139 views

How to prevent duplicated records when applying ORDER BY NEWID() to fetch them randomly? [closed]

I tried using the solution provided in http://stackoverflow.com/questions/1117761/sql-query-to-return-rows-in-random-order to fetch random records in my query. But I have to add NEWID() to the list of ...
2
votes
1answer
153 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...
0
votes
3answers
686 views

how to grant select access my sql server

when I exec the syntax to gain the access I get an error that I cant grant permissions to my self. any Ideas please!
-2
votes
1answer
94 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...
3
votes
1answer
188 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
1
vote
1answer
55 views

Altering strings while selecting them

I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but ...
-1
votes
2answers
1k views

How to do division from select statements in sql server?

I am trying to create a select statement that can return a table of 1 column. Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I ...
-1
votes
3answers
113 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...
-4
votes
2answers
2k views

Get Hierarchial Data in Self-Referencing Table (Parents , Childs) [closed]

In the following Fiddle, sqlfiddle I will pass a sno and I need all the parents of that sno in a table. And all the children of that sno in another table Please refer to this question. Since ...
-1
votes
2answers
724 views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...
0
votes
2answers
555 views

Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question. As stated, this query does return me 95% of the data I need -- SELECT ...
3
votes
1answer
235 views

SELECT multiple sensor values in one query

Background I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
3
votes
2answers
1k views

Select query according to date

We have a table to store the Events. Table has got a schema like EventID INT Primary Key EventName NVARCHAR(100) StartDate Datetime EndDate Datetime So in the front end we want to show the events ...
4
votes
2answers
922 views

WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong? SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable WHERE MyID = 2074163 AND MTR >= 0 ...
2
votes
1answer
1k views

SQL Select WHERE row updated

USE [RF_WORLD] GO /****** Object: Trigger [dbo].[lastdeathtrigg] Script Date: 08/20/2012 10:52:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ...
3
votes
5answers
355 views

Single query to return counts over different IDs in a single record

select count(title_id)as algodata from titles where pub_id =1389 select count(title_id)as binnet from titles where pub_id =0877 select count(title_id)as newmoon from titles where pub_id =0736 ...
1
vote
1answer
465 views

How to avoid duplicating information in a series of nested SELECT statements?

I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per ...
2
votes
2answers
325 views

How can we automatically replace a table with a view for a specific user?

We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a ...
0
votes
3answers
445 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
1
vote
2answers
85 views

Manage select results in sql

We need to manipulate a set of records returned from a select query. This can be done in : Either by saving the whole record to a table variable and then do the required process. We can do like this ...
3
votes
2answers
4k views

Can someone explain why select with nolock will query a potion of updated data?

I was reading the answer from here (from stackoverflow, I think should ask in here) NOLOCK means placing no locks at all. Your query may returns portions of data as of before UPDATE and ...
4
votes
2answers
1k views

Has SELECT an INSERT clause?

I know the form Select * into new_table from old_table statement. Bur recently I found 2 posts here and here which use it like SELECT * FROM NhlPlayer INSERT INTO PlayerBackups I find nothing in ...
6
votes
2answers
4k views

How to use variables inside a select (SQL Server)?

If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice? Example: SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as ...
2
votes
1answer
165 views

Query table A for any rows that are like any search term in table B

I need some help writing a select statement (I still don't think in sets!). Here's the scenario: I have table A with column "Name varchar(20)". I have table B with column "SearchTerm varchar(20)". I ...