Tagged Questions
1
vote
3answers
49 views
Nonclustered index is faster than clustered index?
Both tables have same structure and 19972 rows in each table.
for practicing indexing, i created both tables having same structure and created
clustered index on persontb(BusinessEntityID)
and
...
0
votes
1answer
56 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
5
votes
2answers
49 views
Given two known statements, how can I replicate deadlock?
I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
3
votes
1answer
102 views
Need to divide single column into multiple columns
My table has data with a single column:
1
2
3
4
5
6
7
8
9
10
And I want output like this:
1 2 3 4 5-first row
6 7 8 9 10 - second row
Another has two columns like this:
Column 1 ...
2
votes
4answers
242 views
High CPU usage on SQL server - Slow queries [closed]
Our MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
3
votes
0answers
29 views
High CPU usage on SQL server - Slow queries [duplicate]
Our MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
1
vote
0answers
45 views
Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?
We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough.
Table definition (with columns omitted ...
1
vote
1answer
73 views
Fastest way to insert 30 thousand rows in SQL Server
I have to insert 30000 ids in a temporary table ##MyTempTable that has only one column (ID int) and I would like to know which way I could do it fastest.
I have tried 30000 normal inserts
insert ...
2
votes
1answer
87 views
How to partition a very large table with limited db space?
I have a very big table. I want to partition it, but I cannot.
Database size: 1TB, Free space 200GB
Table:
Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data.
...
4
votes
2answers
79 views
Difference Between SP with BEGIN/END and without BEGIN/END
I have found some SP(stored procedure) written as,
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
BEGIN
--- Actual Work
END
and some as
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
--- ...
-1
votes
1answer
29 views
BULK INSERT in SQL
I have a text file , entries of which i want to insert into a table in my DB.
My query looks like this:
BULK INSERT myDepartment FROM 'C:\myDepartment-c-t.txt'
WITH (
DATAFILETYPE = 'char',
...
0
votes
0answers
36 views
The SQL Server (MSSQLSERVER) service terminated with service-specific error 1814 (0x716) [closed]
My server tempDB size was around 26 GB, I tried to change the path for this by using below script.
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ...
1
vote
2answers
47 views
Database reporting alerts
I am about to make a reporting/alerting mechanism that will periodically examine state of one of my databases that is constantly updated and send reports/fire alerts if needed depending on the data in ...
0
votes
0answers
26 views
SQL Server 2012 linked server to SQL Server 2000
I am trying to select through an linked server from SQL Server 2012 to SQL Server 2000.
The linked server has been setup and I can browser the target database through the linked server which ...
-1
votes
0answers
38 views
Optimize SQL Select Command [closed]
Is there anyway to speed this up by optimizing/condensing the code?
SELECT fullName, email_address, pos_desc, EXT
FROM dbo.BTC_WEB_EMPLOYEE_VIEW
WHERE grp_cde = 'STAFF'
OR grp_cde = 'PROF'
OR ...
4
votes
2answers
56 views
What is the best way to migrate multiple databases?
I want to migrate my database server to a new server. Right now I have a database server with Windows Server 2008 and now I am migrating to a new, separate server with Windows Server 2012.
There are ...
1
vote
4answers
96 views
Can I add a unique constraint that ignores existing violations?
I have a table which currently has duplicate values in a column.
I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.
Can I create a ...
1
vote
2answers
50 views
interpreting how much data has been lost by analyzing dbcc output
We had a SAN outage and this corrupted several databases with no backups.
We ran this to do a repair:
EXEC sp_resetstatus 'test'
ALTER DATABASE test SET EMERGENCY
DBCC CheckDB ('test')
ALTER ...
2
votes
1answer
64 views
Create a sub query for sum data as a new column in SQL Server
Suppose that I have a table named tblTemp which has this data:
| ID | AMOUNT |
----------------
| 1 | 10 |
| 1-1 | 20 |
| 1-2 | 30 |
| 1-3 | 40 |
| 2 | 50 |
| 3 | 60 |
...
0
votes
4answers
39 views
SQL Server transaction log growing without control
We have a database growing uncontrolled and filling the hard drive every 2 days. Database maximum log size is set to 6 gb but id doesn't seem to be working because it continues growing beyond 6 gb.
...
1
vote
1answer
49 views
Sql Server Server Group query
Scenario
I have 4 front-end sql server.
All of them have the same db instances. Data are replicated from back-end databases.
Issue
In SQL Management Studio I have registered the servers and I have ...
0
votes
1answer
37 views
Displaying multiple data in a column [closed]
I have the following table structure:
Table A: id, rname, idB, idS
1, Learning, 11, 22
Table B: idB, Bname
11, Astronomy
111, Building
Table S: idS, Sname
22, Google
222, Freddy
Table ...
3
votes
2answers
191 views
Reduce Clustered Index seek cost SQL Server 2008 R2
I am running a query and it have records in lakhs, it takes more than 20 mins to fetch data. After running the execution plan i noticed that clustered index seek cost can be be the reason. How can I ...
1
vote
1answer
66 views
Autogrow of database1_log file in database database1 took 1010871 milliseconds
in past i had a problem of that query been executed for 7 days. i couldnt find any solution. so i created again new database with indexes.
Now i am running a simple query:
UPDATE query
query has ...
3
votes
1answer
31 views
Verify Query Request Received
I have a software system which queries data via OLEDB from an external SQL Server 2005. The query request is sent every 15 minutes. I want to verify that the SQL Server is receiving the query ...
4
votes
1answer
44 views
Execute output from query
I've got this query that builds my drop login statements. As part two I want to execute this output. Ideally I want to do this in a sql job with two steps (generate and execute). Can this be done?
...
2
votes
3answers
132 views
Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?
Does the SQL Server CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN that evaluates to true? If it does go thru the entire set of conditions does that mean that the ...
0
votes
0answers
20 views
Aggregate query on multiple values of same field [migrated]
I am using sql server 2012, I have a table of credit card transactions as follows:
CardScheme RespCode
=========================
Visa Declined
MasterCard Approved
MasterCard Approved
...
6
votes
2answers
72 views
Turning OFF CHECK_POLICY by default
We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option
CHECK_POLICY = OFF;
After creating a user, I have to run the command
...
7
votes
2answers
525 views
Is it a bad practice to always create a transaction?
Is it a bad practice to always create a transaction?
For example, it is a good practice to create a transaction for nothing but one simple SELECT?
What is the cost of creating a transaction when it ...
0
votes
1answer
41 views
Restoring database backup file gives access error
I am running SQL server 2008 and trying to restore from a backup file.
Note: the backup file and folder and read/write access to "everyone". Running the command as "master"
Running
RESTORE ...
4
votes
3answers
56 views
Check server activity with code
I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the ...
-1
votes
1answer
64 views
validate where each parent intermediary is also a parent to itself [duplicate]
I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any.
I need to do is first of all get all parents ...
0
votes
1answer
63 views
Remove specific duplicates (all but latest)
When removing duplicate rows, using the below query from this tutorial, how would I go about forcing which of the found duplicates to remove?
DELETE FROM dbo.ATTENDANCE
WHERE AUTOID NOT IN ...
9
votes
3answers
853 views
Updating a table with millions of records, its been 4 days
I am currently updating a table with millions of records, its been 4 days and query is still executing.
I checked the activity monitor its shows that query is running.
In event log there is no ...
-2
votes
0answers
50 views
SQLYzer experience [closed]
recently found and installed the 30 days free trial version of Precise SQLYzer (just google it and you'll find it easily). I tried it on my UAT env and so far I find it really great. I'm wondering ...
1
vote
2answers
78 views
Remove scientific notation from an XML column
I have a XML column as shown below:
<Root>
<Row>
<Rowid>1</Rowid>
<date>2013-05-06</date>
<Balance>1.0002E7</Balance>
</Row>
...
0
votes
1answer
41 views
Upload (someone else's data) to Postgres, SQL Server [closed]
I work for a company that does program evaluations for state agencies. Thus, I frequently work with someone else's data. They send me the data via sftp:/, mail, etc. and I load it into either SQL ...
2
votes
4answers
117 views
When should I use the shrink option
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
4
votes
1answer
72 views
How to get a row_number to have the behavior of dense_rank
I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
4
votes
1answer
72 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
3
votes
6answers
143 views
Backup very large table
I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
4
votes
2answers
96 views
Should I use a composite or single-column index?
I have the following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
4
votes
1answer
568 views
CTE memory space [closed]
How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query?
I have a CTE that is ...
-1
votes
1answer
56 views
Single quote issue
I have to update a column where I am giving query which is
Update Table
Set col = '[189] IS NOT NULL and [189] <> '''
Where colid= 198
But it's giving output:- [189] IS NOT NULL and ...
0
votes
2answers
83 views
Running a query within a scheduled job
I am looking at running the following query within a scheduled job weekly:
Select TOP 10
s.database_name,
m.physical_device_name,
CAST(DATEDIFF(second, ...
-2
votes
3answers
97 views
Left Join not giving desired result
I have two Datasets
FEES PAYMENTS
CrsCode InstNo FEE Regno CRSCODE Instno Payment
CA1 -2 100 R1 CA1 -2 100
CA1 -1 200 ...
-1
votes
2answers
65 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 ...
2
votes
1answer
72 views
Reorganize full text catalog is offline or online?
I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to ...
3
votes
3answers
150 views
Fixed length text file insert into SQL table
I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic.
I have 100's of text files that ...
