Hot answers tagged sql-server
11
Short version: seek is much better
Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...
9
This doesn't look like a permissions issue at all, but rather a bug in the UI.
If you are using Management Studio Express, then you should consider two options:
Ensuring you are on the most recent service pack (yes they apply to client tools as well), and perhaps the most recent cumulative update as well.
Many of the bugs in the 2008/2008 R2 version of ...
7
Is it “legal” TSQL to CREATE TABLE #SomeTable and then DROP TABLE #SomeTable repeatedly in a script?
No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:
IF 1 = 1
BEGIN
...
6
Your issue isn't with the 32-bit vs. 64-bit, but it's that you are trying to restore a database on an older version.
The database was backed up on a server running version 10.50.2600.
That version is incompatible with this server, which is running
version 10.00.4064.
This means that the source DB is 2008 R2, and destination DB version is 2008 (not ...
6
If we set autgrowth for log file as restricted then logs size will increase up to specified limit and then it will start discarding old logs and new logs will be there in logs.
This is wrong. If it has to grow it will grow until the max size and after which further growth request will result in failure. You are confusing things with log truncation, ...
6
Parameter sniffing is your friend almost all of the time and you should write your queries so that it can be used. Parameter sniffing helps building the plan for you using the parameter values available when the query is compiled. The dark side of parameter sniffing is when the values used when compiling the query is not optimal for the queries to come.
The ...
6
If your database has 16 MB of free space, and the drive it's on has plenty of free space, then don't worry about it. As long as there is sufficient space and you haven't disabled the database's ability to autogrow, then the database will grow the data file when it needs to. Of course you should set your data file's autogrow to some realistic increment, ...
6
Assuming Hire_Date is DATETIME you should do this:
SELECT e.LastName, e.HireDate -- properly denote which table a column comes from
FROM dbo.EmployeeT AS e -- use schema prefix and use meaningful aliases
INNER JOIN dbo.Job_TitleT AS t -- use proper, explicit JOINs instead of 1970s syntax
ON e.Job_TitleID = t.Job_TitleID
WHERE e.Hire_Date >= ...
6
There is no knob or trace flag that I know of to coerce this behavior in any way (though Paul White mentions some trace flags here that provide more visibility and allow you to coax some behavior deltas).
Microsoft provides plenty of weapons but this one would almost be guaranteed to be pointing squarely at your own feet 100% of the time. When running a ...
5
High CPU in SQL Server is very often caused by poor indexing
Unfortunately, SQL Server 2000 lacks the tools of later versions to track these down easily
Saying that, if you run SQL Profiler you will be able to find high CPU queries and start looking at query plans to work out what indexes are missing
5
Can partitioning the table help reduce the completion time?
No. Partitioning is not a performance feature, is used for other purposes. If the table has 120 million rows unpartitioned, it will also have 120 million rows after partitioning. Read How To Decide if You Should Use Table Partitioning.
If you want to improve performance you need to identify ...
5
This error does not sound like a problem with the password at all, but rather a connectivity issue. It could be that some policy is re-establishing firewall rules, or it could be simply that you have flaky DNS, an unreliable connection to the domain controller, or some other network issue.
As @Thomas pointed out, the "pipe" in the error message implies that ...
5
Your comments note that you're specifically talking about full-text indexing.
You can indeed query the database while a full-text index is being created. Full text indexes are created in the background on SQL Server 2005, 2008, and 2008R2.
You can continue to query the database using the LIKE operator, although of course the queries won't be as fast as a ...
5
An MDF files is not the database, as such. It is merely the on-disk storage used by the SQL Server database engine.
When your app "uses" the MDF, it is actually attaching them to an SQL Server install and running queries against that. This is transparent really.
Instead of this, you can install SQL Server on the network (even Express edition), attach tehe ...
5
Well this won't happen unless
a trigger is firing on the target table, and the error happens there
the INSERT does some processing that adds enough data to make it too wide
Also, what happens with this please? This can not fail unless there is some processing.
INSERT INTO table (col1, col2)
SELECT LEFT(column1, 200)
,column2
FROM tmpTable
4
FILLFACTOR only applies when you build or rebuild an index, not during normal operation. Normal operations always try to fill the pages to 100%.
If you insert a row that has a variable width, then update the row to be longer, that row will no longer fit on the page if there isn't enough extra space to store the after-image on the same page. If there isn't ...
4
This is an open question with no clear choice. YMMV so you have to test. Here is my opinion:
Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to ...
4
You are lost on Auto Grow, you are lost on File Groups. For your own sake, I hope this is not a production DB.
Having said that - here is how the DB spacing works:
Execute this in SQL Server Management Studio
sp_helpdb <yourdbname>
This should give you 2 sets of output.The first has a column called db_size, which will give you the current DB size. ...
4
In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.
USE CentralDB;
GO
CREATE TABLE dbo.Dates(...);
GO
USE OtherDB;
GO
CREATE SYNONYM dbo.Dates
FOR CentralDB.dbo.Dates;
Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note ...
4
I'd suggest instead that you just move the database to the new, bigger drive. In addition to avoiding the complexity of splitting your database across multiple drives, you also free up disk space for the other databases that will stay on the original disk.
First, execute the following:
USE yourdb;
GO
EXEC sp_helpfile;
In most cases you should get two ...
4
This means that database users have no matching server logins.
That is, each database sys.database_principals has no match in sys.server_principals
For Windows logins this is easy. This generates your missing CREATE LOGINS
USE MyDB
SELECT
'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(sid)) + ' FROM WINDOWS'
FROM
sys.database_principals DP
WHERE
...
3
You can’t do this by default because you are trying to restore newer version of backup to older version of sql server.
Solution for this is to script object and data and then to execute scripts on new server.
You can also try using some database comparison tools such as those from ApexSQL (Diff and Data Diff) or Red Gate (SQL Compare and SQL Data Compare). ...
3
This is an error which shows that an MS SQL Server install should come before Visual Studio.
This issue occurs when you install first Visual Studio 2008 then your try to install SQL Server 2008 R2. For fixing this issue follow below steps:
download Visual Studio 2008 sp1
then install SP1
after it install SQL Server
(or)
uninstall Visual Studio 2008 ...
3
Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.
Here is there full response:
...
3
If you have Enterprise Edition, are on 2008 or greater (always useful to specify version), and most or all of the values in this column are less than the upper bound of INT, you can get all the space back and probably quite a bit more by applying data compression.
My guess is you can improve I/O by at least as much, probably more depending on the nature of ...
3
Try Version 7.0 available here that supports Operating system:
Windows XP/2003/Vista/Server 2008/7.
Look like Microsoft does not have it on their site. the latest is 8.0 which supports only Windows 7, Windows 8, Windows 8 Pro, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Vista Business, Windows Vista Enterprise, Windows Vista ...
3
You can use cross apply and the third parameter of charindex to get the position of the underscores.
declare @T table
(
Name_Level_Class_Section varchar(25)
)
insert into @T values
('Jacky_1_B2_23'),
('Johnhy_1_B2_24'),
('Peter_2_A5_3')
select substring(Name_Level_Class_Section, P2.Pos + 1, P3.Pos - P2.Pos - 1)
from @T
cross apply (select ...
3
Based on feedback in the comments on this answer, the situation is this:
There is an explicitly-created BUILTIN\Administrators group login in SQL Server that has been denied CONNECT to the database engine.
There is no other sysadmin-level login available, including the NT AUTHORITY\SYSTEM login that gets created by default.
SQL Server does not allow a ...
3
First of all, Welcome to dba.stackexchange.com and thanks for your post !!
Is it possible to limit what values are allowed in a coulumn based off of other values in the row.
Yes using CHECK CONSTRAINTS as described here
Example :
create table myTable (ID int identity(1,1)
, Test_mode int
, Active int
...
3
Three things immediately spring to mind:
Master Data Services, part of the SQL Server arsenal.
Roll your own - build a central database server to store this data; have the other servers link to that server for this information. This matches your "single point of truth" description, but may not be entirely practical.
Use software to automate the rollout / ...
Only top voted, non community-wiki answers of a minimum length are eligible
