Slava Murygin
  • Member for 5 years, 7 months
  • Last seen more than a month ago
Please explain the difference between row and page compression
7 votes

In order to make a decision for bunch of tables you can run procedure "sp_estimate_data_compression_savings": exec sys.sp_estimate_data_compression_savings @Schema,@Table,NULL,NULL,ROW; exec sys....

View answer
Writing SQL to check for a role if exist and add member
3 votes

If you want to check existing roles look at this table: SELECT * FROM sys.database_principals WHERE Type = 'R' If you want to check existing role members try this query: SELECT Role = r.name, ...

View answer
Understanding SQL Server statistics better
Accepted answer
3 votes

Very easy. Look for the result of: select datediff(day,'1/1/1950','12/31/2007'); It will give you your DISTINCT_RANGE_ROWS + 1 That means that SQL Server thinks that all your DOBs are approximately ...

View answer
How to detect bad execution plans automatically?
Accepted answer
2 votes

I've had that problem for only on query: 1. Scheduled job was looking in query cache for particular query. 2. When query is found it looked for bad pattern in plan. For instance: Index Seek for index ...

View answer
Meaning of terms in SQL Server execution plan
2 votes

Estimated sub tree cost is a SUM of cost of all operators preceding the one you are looking at. The easiest example is to look at the left-most icon - it will have an Estimated sub tree cost of whole ...

View answer
Given the same input, is the HASHBYTES function guaranteed to return the same output?
1 votes

Have similar problem with HASHBYTES. Same values from one "varchar(max)" column generate multiple hash values. The problem is caused by capitalization and Case Insensitive collation. Because ...

View answer
How to get Current Shift?
1 votes

Why don't you simple enter 2 records for Second shift? It will solve your problem. insert into #ShiftMaster values ('SHF1', 'Shift A', 'First Shift', '07:15:01', '15:45:00'), ('SHF2', 'Shift B', '...

View answer
Opening a stored procedure file multiple times in sql server management studio
1 votes

You did not get the point of SQL programming. You can have 1000 copies on multiple machines of a script to create/alter an object, but until one of those scripts is executed against a server, that ...

View answer
SSISDB user "AllSchemaOwner" is not SQL Server Login
Accepted answer
1 votes

Finally found an answer: You can create database user, which won't have a SQL Login and won't be able to authenticate, but will have permissions and can be specified in WITH EXECUTE AS clause for ...

View answer
On the query planners's reasons for choosing merge joins vs nested loops
1 votes

In your sample case SQL Server made very good choice. Here is what it was actually doing: - In the first set of data it extracts 10 rows from Fact table based on the value column. Then it extracts 10 ...

View answer
Which indexes for joining a table onto itself on two columns
Accepted answer
1 votes

There are 2 options: 1. Create clustered index by account_id. Delete all other indexes. 2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-...

View answer
SQL Server - How is a row updated under the hood?
1 votes

I'm not a replication guru, but record may be marked as deleted only in case when an update is generating Page Split. For the case of replication you can test it by looking at DBCC PAGE and look ...

View answer
KILLED/ROLLBACK query doesn't stop running
1 votes

You have to be very careful. Assuming your query is not restarting and you have a rollback for that SPID. If you restart SQL Server it won't help, because transaction still would have to be rolled ...

View answer
Compare two tables with same structure, but differing membership numbers
Accepted answer
1 votes

That should give you what you want: SELECT UT.Name, T1.*, T2.membership_no as 'Unmatching in membership_no Second table' FROM T1 INNER JOIN UT ON T1.user_id = UT.user_id INNER JOIN T2 ON T1.user_id ...

View answer
unistall sql server 2005
0 votes

Not sure if it will solve your issue but you can try something like this: start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 ...

View answer
Shrink data MDF file on server running on low disk space
Accepted answer
0 votes

Your sp_spaceused provides 2 sets of the data: - DB Size + Unallocated space - These numbers include BOTH: Data and log file; - Total statistics of RESERVED space for all objects within the database; ...

View answer
Insert into temp table is taking longer than temp variable
0 votes

You haven't not provided query plan for following portion of IO statistics: SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms. SQL Server parse and compile time: CPU time = 65 ...

View answer
MS-SQL stored procedure to insert into one-to-many-to-many relationship
0 votes

Maybe something like this: SELECT r.line_id, r.descript FROM reqitems as r INNER JOIN @lines as l ON r.descript = l.descript WHERE r.req_id = @req_id; Then you link it to other values to insert ...

View answer
Which indices should I create?
0 votes

At first I'd suggest to have group_id and visit_id in INT or at least BIGINT. If that table has only 3 columns it might worth to create not-unique clustered index by group_id and unique constraint on ...

View answer
database inaccessible and stuck in startup state
Accepted answer
0 votes

What do I want now? Remove these databases in any way possible, so that they don't consume any resources. I don't mind trying unconventional methods. To remove them just stop SQL Server and delete ...

View answer
Space requirements for Index maintenance
0 votes

That depends on a type of index operation. If it is REORGANIZE the impact is minimal, but it can be tremendous amount of I/O associated to a process and enormous amount of time. If you plan to do a ...

View answer
Using results from one query as the parameters for another
0 votes

Use local Temp Table in your procedure: SELECT id INTO #X FROM ABC WHERE MyCriteria = 'XYZ'; SELECT * FROM ABC WHERE Cancelled = 'Yes' and id in (SELECT id FROM #X); UPDATE ABC SET MyColumn = ...

View answer
INSERT INTO SQL Server : Linked Server without specifying database in semi-fully qualified name
0 votes

Yes you can. Use SQL synonyms https://msdn.microsoft.com/en-us/library/ms187552(v=sql.105).aspx By doing that you'll have a full control behind the curtains, but please fully document it and be ...

View answer
How Plan Explorer Group Operators in Query Columns Tab
0 votes

At first, it looks like you've modified XML, because the query does not exactly match the plan. At second, format your query like this SELECT o2.Column1, o2.Column2, o1.Column3, ...

View answer
SQL Server Management Studio - Processor counters for different sessions do not add up
0 votes

You are measuring CPU time, not real SQL Server query execution time or real CPU usage. Why do you expect these numbers to add up? You have to use SQL Profiler or Extended events to capture CPU usage ...

View answer
SQL Server - Why does importing lots of columns with few rows takes way longer than few columns with lots of rows?
0 votes

At first, I guess the size of the 268 columns can be pretty big. So just look at number of bytes in one row, multiply by 128 and you'll see how much data SQL can cache at one time. Second, there ...

View answer
Clustered Index and PK - Two different Objects?
-2 votes

When you create Index or PK, SQL never creates a "File". When you create PK, it will be clustered by default, unless you already have clustered index or specified PK as NON CLUSTERED.

View answer