Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

0
votes
1answer
86 views

Is there any way to get recently inserted value in a table without using aggregate functions?

I tried to get the users list who are all accessed our application in past 6 months . In our table , we are storing login_time , user_id ..etc . It's a huge table approximately it will contain over 3 ...
4
votes
2answers
1k views

Backup Only Database Schema Everynight

I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't ...
2
votes
3answers
3k views

SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows. SELECT dbo.Articles_in_Consignment.Consignment_id, dbo.ORDER_D.Orders_boxsize, ...
0
votes
2answers
3k views

How to select row and column name where field is null?

I wanted to show row and column where field is null. i already see this example here but i don't understand xml code. my code is like this if exists (select * from Header where [name] is Null ...
1
vote
1answer
304 views

Delete word, its meanings, its meaning's example sentences from DB

I have three tables as below (simplified for demonstration): words ===== integer id text word meanings ======== integer id integer word_id text meaning examples ======== integer id integer ...
0
votes
2answers
2k views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
1
vote
1answer
178 views

cloud computer to speed up sql big data queries?

i have been doing a lot of data mining at my new job. we collect TONS of data, and it's my job to visualize that sea of information in real time with statistic/heuristic analysis. we're currently ...
2
votes
1answer
216 views

Finding similar values in columns

For example I have a column id and column email. I would like to find all values from id and column that share similar pattern. let's say: in id 'column' I have Alexander and in 'email' column - ...
1
vote
1answer
421 views

MAX function and group by usage - postgresql

I am building a ranking algorithm using IMDB's formula: http://stackoverflow.com/questions/1411199/what-is-a-better-way-to-sort-by-a-5-star-rating/1411268#1411268 The formula for calculating the Top ...
4
votes
2answers
229 views

Performance on SQL Join

I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK. The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits So RN1234567 for ...
1
vote
1answer
6k views

Retrieving data from inner join using LIMIT and OFFSET

These are my two tables: table1 qid[PK] |gid[PK] |abcd | xyz | date ---------------+---------+---------+------+------------ 00001 | qwe | 54 | a | 1994-11-29 00002 ...
5
votes
1answer
1k views

sql_slovak_cp1250_ci_as vs Slovak_CI_AS

Can someone explain to me what is the difference between collation sql_slovak_cp1250_ci_as vs Slovak_CI_AS? I have a db set to sql_slovak_cp1250_ci_as and shared hosting supports only Slovak_CI_AS. ...
1
vote
1answer
165 views

Setting up simple relational database structure

I'd like to set up simple MySQL database but I'm a bit confused about everything, so let me try to explain... I have 4 relevant tables, let's call them POS, POS_STRUCT, POS_STRUCT_DIV and MEMBERS. ...
2
votes
1answer
397 views

sorting groups of related rows by average values while keeping the groups together

I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible. Consider the following table. corrmodel=# SELECT * from data limit 1; id | datasubgroup_id | datafile_id | ...
5
votes
3answers
3k views

SQL Server 2012 database backup successfully report but no backup file

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must ...
-2
votes
2answers
26k views

Dynamic pivot: sum of sales per month

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to ...
4
votes
1answer
1k views

Error while restoring database in SQL Server 2008 R2

This error occurs while I restore database from .bak file A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software ...
0
votes
1answer
202 views

How to create an index view?

I have a view which is doing summation of several columns: Select cola, colb, cola + colb + colc from table tbl_a Can I know what are the indexes I should create for the view to improve its ...
1
vote
3answers
194 views

Efficient SQL database design for recording examples of a word

I am designing a dictionary-like program which stores (and displays) a few example sentences of the given word. One way to design a SQL database for such problem is to create a table with two columns ...
0
votes
2answers
800 views

Remote Desktop Session's Machine ID for SQL Server Query

How to get machine name of a user, who is connected on an instance through Remote Desktop and used windows authentication login. I need it for each query she has executed. sys.dm_exec_connections just ...
1
vote
1answer
6k views

Possible Causes of a Slow Sql View When Filtered

I have a view in Sql Server 2008 that when performing just a: SELECT * FROM [Table] Will return roughly 40000 rows by 130 columns. Here are 6 runs with client statistics on (roughly 7 seconds ...
2
votes
1answer
818 views

Parsing and binding variables for SELECT .. WHERE column IN ( .. ) queries

I'm looking to make sure that all queries in a php web application have proper use of bind variables to minimize parsing of the queries. I'm wondering how Oracle parses queries that compares a ...
1
vote
1answer
1k views

Sql Server rollback to some point in time

This seems like a fairly common scenario, yet I'm not sure how to do this. I have a very large database (think a lot of terabytes), which makes it impossible to create a full backup. At some point, I ...
1
vote
3answers
6k views

How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1?

I have two database - Database1 and Database2. Both database contain a table that have similar structure as follows: ...
5
votes
2answers
1k views

T-SQL Purpose of MAX in this Group By Query

I have run into some pre-existing SQL that I'm having a hard time uderstanding. SELECT MAX(I.Symbol) Symbol , MAX(I.Ticker) CUSIP , MAX(I.Name) Name , SUM(H.Quantity) TotalQuantity , ...
2
votes
1answer
449 views

Group activities in activity feed by users and products

On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there. One of them is the grouping. I've built it so that if multiple users buys ...
0
votes
2answers
778 views

How can I simplify this query?

I have written the following SQLite query: SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight' FROM ( SELECT "transaction".transamount AS ...
1
vote
1answer
9k views

How to check for null in column? and use other column instead?

How would I write a query that involves the following check: if colA is null then colB = colB1 + colB2 if colA is not null then colB = colA
4
votes
2answers
1k views

Right Trimming Binary Data in SQL Server

Scenario: I am inserting a string into a binary field (CONTEXT_INFO) and then later attempting to pull it out and convert it back to a string. When I do, the resulting string has a length of 128 ...
1
vote
2answers
579 views

Database Design - One table from many source(One to One)

I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, ...
2
votes
2answers
8k views

Multiple triggers vs a single trigger [closed]

Scenario: Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed. Question Given that the solution is to use database ...
5
votes
1answer
8k views

What is the difference between NORECOVERY and RECOVERY when restoring database?

Can anyone explain the main differences? Any help appreciated.
2
votes
1answer
230 views

is it reasonable to use NoSQL for my project

Imagine you want to write a webapp for selling your car online. A car has a lot of attributes. Like how many doors, color, aircondition, etc. Some of them are simple boolean values, others might need ...
2
votes
2answers
628 views

Advantages of Database Snapshot

Can someone please highlight the advantages of Database Snapshot in SQL2008. I read some where that "When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent to the ...
1
vote
1answer
104 views

T-SQL Case functionality

Is it possible to structure a CASE statement as follows? For each Path column I want to update the Value column, but the values aren't being set and I'm not sure how to proceed. The columns are all ...
4
votes
1answer
1k views

Removing duplicates from a query with conditions in Access 2007

I have a table of transactions with 880,095 records that I am querying. There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table. Eg 1. ...
0
votes
0answers
32 views

Max connections sql server express could have? [duplicate]

Possible Duplicate: Max connections SQL Server Express could have? I have sql server 2005 express edition installed on one of my client's machine, and lots of other users are also connected ...
1
vote
1answer
844 views

Restore .mdf data file for a File Group in SQL Server

I have a database with three filegroups and each filegroup hosting one datafile. One of the secondary filegroups XYZ with data file xyz.ndf got updated with wrong/bad information in various tables ...
1
vote
2answers
2k views

Changing field length when foreign keys reference primary key field in table

I have a situation where a number of foreign keys are referencing a column in Table "A". I would like to change the length of the column in table "A". What is the best way to accomplish this in SQL ...
4
votes
1answer
495 views

Large Transaction Log Backups in Service Broker-enabled system

We have a system that uses Service Broker for asynchronous messaging, with a fairly high throughput of messages. We don't really care about the persistent features of SSB in this scenario -- the ...
3
votes
3answers
1k views

SQL Server 2008 R2 Restore a back up to a new database

I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records. What is the best way to do this? ...
3
votes
2answers
2k views

How can I capture queries that fail on the SQL Server due to connection failures?

How can I capture queries that fail on the SQL Server due to connection failures? Profiler doesnt seem to capture if there is a connectivity loss. This is scenario: DB server running in Domain A ...
6
votes
2answers
1k views

Deleting duplicate records when using text datatype

I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id. Normally ...
14
votes
5answers
3k views

Sequential GUID or bigint for 'huge' database table PK

I know this type of question comes up a lot, but I've yet to read any compelling arguments to help me make this decision. Please bear with me! I have a huge database - it grows by about 10,000,000 ...
3
votes
3answers
9k views

Which SQL Server Express edition is right for me?

I have developed a Java program which works with SQL Server as it's database. I have installed SQL Server 2012 Enterprise edition on my Windows and designed the database via SQL Server Management ...
4
votes
2answers
362 views

Inner Join or direct data save in SQL Server

I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its ...
0
votes
1answer
193 views

How to number SQL results sequentially?

Given following query - SELECT name, age FROM students WHERE age > 22 The result is - name age ---- --- Kshitiz 23 Steve 22 Paul 24 How can I number the results sequentially? Like ...
4
votes
1answer
88 views

a query that shows the load request received through GPRS by salesman

I have mobile sales system, in which the salesman sends their stock request in handheld device and through GPRS the data comes to MS SQL database. I need o show a report that shows the which request ...
3
votes
1answer
14k views

Multiple “GROUP_CONCAT”s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
5
votes
1answer
187 views

Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery

Our end users have many ways of shipping product to our customers. They can use the customer address in one table ([customer]), a specific delivery address in another ([dropship]), or yet another ...