An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

1
vote
1answer
580 views

Why is my maintenance plan save overwriting job ownership?

I'm slowly untying all the system dependencies to the previous DBA's system account and moving them to a service account. The old DBA had ownership of all the jobs in our maintenance plan. In SSMS I ...
0
votes
3answers
133 views

Is it possible to check a date in SQL?

I'm trying to write a Microsoft SQL Server stored procedure which takes an account ID, and returns an authentication ticket and whether the ticket is expired. Something like this: SELECT ...
0
votes
1answer
100 views

Regular expression problem

SELECT FIRSTNAME,LASTNAME, EMAIL FROM CUSTOMERS WHERE REGEXP_LIKE(EMAIL,'[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]','i'); I know this is not practical or make a ton of sense, but its a ...
2
votes
1answer
2k views

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

I have to restore a DB table every day from a postgresql 8.4 to 8.1. For the backup, I use this code (windows server): D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > ...
2
votes
1answer
348 views

Table Statistics Affect Index Only Query?

We have a query that was getting poor performance. The root of the problem could be reproduced using a simple query accessing only one index to retrieve one column (the indexed column) from eight ...
11
votes
7answers
11k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...
2
votes
1answer
242 views

Execute Queries Against CMS within SSRS Reports?

Is it possible to execute queries against a Central Management Server (CMS) using SSRS? For example, I know of no way to tell a stored procedure that it is inherently supposed to call the CMS, so it ...
-2
votes
1answer
686 views

Query database by date, month, year

How can I query order by date. Like this: And the same for week, month, year...?
2
votes
1answer
1k views

Save Transaction: Is the name local to the stored procedure or not?

Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused. There is a subset of stored procedures that preserve state on failure. That is, if the ...
6
votes
3answers
2k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
2
votes
2answers
531 views

Tracking of deleted records who delete the transaction in SQL server 2008r2

how can we check who deleted the transaction even after few days in sql server 2008 r2 is there any command instead of logs
1
vote
2answers
2k views

Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of CREATE TABLE column1 ( first_id int(11) NOT NULL AUTO_INCREMENT, ...
1
vote
2answers
158 views

Is it possible to have indexes for like '%*%' type queries

Now for my thesis project i need to make a lot of queries like the below select * from tblMyTable where ItemName like '%item%' Now my question : is that possible to create indexes for such queries ...
17
votes
5answers
5k views

SQL: What is slowing down INSERTs if not CPU or IO?

We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower ...
4
votes
2answers
617 views

How to sync a database with an external full text engine?

How do you keep your external full-text engine (Lucene, Solr, etc) in sync with your database data? For example, keeping track of inserts, deletes, updates. My current solution involves a big union ...
3
votes
2answers
268 views

SQL Data aggregation

I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database. select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct me.id) ...
3
votes
1answer
154 views

update with nested join problem

First of all newbie here and to the world of Oracle, just trying to get some code working: UPDATE CUSTOMERS SET RE.LASTNAME = 'NO REFERRAL' WHERE RE.LASTNAME IS NULL (SELECT CU.CUSTOMER#, ...
3
votes
1answer
2k views

How do I update a table with the aggregted values (min, max) of a related table, fast?

I have a table create table w ( id integer primary key, -- min_xyz double, max_xyz double, min_abc double, max_abc double ); with the min* and max* values currently being null. I have ...
18
votes
1answer
4k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
1
vote
2answers
160 views

Execution Plans Query

I want to check the estimated number of rows of a query in MS SQL Server. I can right click on the query and select "display estimated execution plan". but I want to do the exact thing with a hard ...
4
votes
2answers
6k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
2
votes
4answers
622 views

Should I use a Column naming convention or rely on column descriptions?

Frankly I am stunned that after some basic (very basic) internet searches I have not been able to find a comprehensive naming convention or guideline or SQL object and column naming. The parallel ...
3
votes
2answers
4k views

How to monitor SQL Server data and log file growth?

I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
1
vote
1answer
1k views

When to open your MySQL cursor

A relatively simple question but from all my research I have been unable to find a decent answer. Which is technically more correct, open your cursor after your handlers have been defined or before ...
6
votes
1answer
2k views

SQL 2008 R2 - Odd timeout issues

I'm having some recent timeout issues in our SQL 2008 R2 server. This started occurring late last week and has since somewhat improved. Issues: When using SSMS and right clicking over a ...
3
votes
2answers
2k views

How to check when statistics was last executed?

We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
2
votes
1answer
544 views

How to serialize a record / row in SQL?

Is there a way to serialize a record/row in SQL? And conversely, to deserialize something that has been serialized. By serialize, I mean put all of the fields in a record into one field or string. ...
-1
votes
3answers
162 views

SQL Server - Transaction Slowness

Using: SQL Server 2008 R2 I'm seeing some behavior right now in SQL Server, and I can't decide if the server is underperforming or not. I've set up a test to insert an integer into a table, as ...
4
votes
1answer
127 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
2
votes
3answers
2k views

How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()?

How do I use sys.dm_exec_connections in combination with SUSER_SNAME()? I want to see the username corresponding to each record in sys.dm_exec_connections.
2
votes
3answers
92 views

transaction log after restore

Can you let me know why a transaction log would be a lot smaller in size after restoring from a full backup. Currently we have a massive log file on the live server, but after a restore to a test / ...
2
votes
1answer
1k views

SQL Server Maintenance Plan Fails to Rebuild the Indexes

When I run a maintenance plan to reorganize and rebuild tables indexes, it fails. Below the last two rows that displays the fail: Source: Reorganize Index Executing query "ALTER INDEX ...
2
votes
1answer
868 views

Access SQL Server from a different subnet

I'm creating a program which accesses MS SQL Server 2008, the problem is that I can run the program smoothly as an administrator account, even on different subnet computers and using terminal service ...
2
votes
1answer
1k views

Tracing/Logging of Sybase ASA

Trying to get logging/tracing of Sybase 9.0.2.3396 (Adaptive Server Anywhere). I wish to log all the SQL executed against the database. I tried RIBO and while I got it "working," I never saw anything ...
1
vote
3answers
234 views

What is this error about in sql server?

Trying to restore database, getting a dialog in SQL Server Management Studio You cannot select backup sets from different databases" Any idea why I am getting this error? BTW backup is from ...
1
vote
2answers
6k views

ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

I got that error when I tried to execute this query: select * from lc_history2 lc where lc.view_name <> 'LC Main' and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc ...
2
votes
1answer
1k views

Database restoring taking too long

I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible. ...
4
votes
2answers
321 views

Getting to know SQL via the “short questions/simple answers style of learning”

Looking for a incremental SQL course that would likely be similar to the this Ruby Koan course; which is in fact inspired by the book, "The Little Lisper", which uses a short questions/simple answers ...
0
votes
1answer
173 views

I want to be databse specialist (SQL) [closed]

Hello there database specialists, I have Bachelor degree and one part of study was database, where I learned about database design, SQL language... and I'm working with mysql, but I decided to be ...
0
votes
1answer
527 views

SET keyword in SQL Server

I have been using SET keyword to assign the values to parameters declared in SQL Server something like, Declare @StudentName Nvarchar(max) SET @StudentName='ABC' I'm not sure how to write this if ...
15
votes
5answers
251 views

Enforcing database integrity

Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc? How much of a performance improvement one can expect for not ...
2
votes
2answers
166 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
1
vote
1answer
110 views

SQL Server function invocation syntax question

The following excerpt is extract from this page. CURRENT_USER (Transact-SQL) Returns the name of the current user. This function is equivalent to USER_NAME(). When I execute the two ...
4
votes
2answers
878 views

Why did Microsoft kill “English Query” on SQL Server?

Given the popularity of the natural language user interface Siri, which was acquired by Apple for $200 million, I've wondered if anyone knows why Microsoft killed "English Query" on SQL Server.
-3
votes
1answer
681 views

Best practices for finding and updating a handful of records?

Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".
7
votes
3answers
7k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
2
votes
2answers
266 views

SQL Server 2008R2 Ent + Win2003 Ent R2 32bit will not use all available memory

server -HP ProLiant DL380 G5 2 proc -2x QuadCore Intel Xeon E5450, 3000 MHz Memory- 12288 Мб (DDR2-667 Fully Buffered ECC DDR2 SDRAM) Set: /3GB in the boot.ini. /PAE in the boot.ini. Enabled AWE ...
2
votes
3answers
131 views

Document versioning

I want to store in SQL Server 2008 XML files, but in a way that versioning is supported. For example, I have a XML document and I save it in the DB(in the XML datatype column). After that, I edit it. ...
4
votes
2answers
484 views

Specify type on SQL Server Computed Column

I have just added a computed column to a table, and it is showing up as an int and I'd like to make it a bit is this possible? I'm having a hard time finding any info on this. Below is the computed ...
5
votes
2answers
584 views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...