Finding contiguous ranges in grouped data I have a table with the following structure:

CREATE TABLE `Rings` (
    ID_RingType CHAR(2),
    Number MEDIUMINT UNSIGNED,
    ID_User INT(11)
);

and with data:

INSERT INTO `Rings` VALUES
  ('AA',1,1),
  ('AA',2,1),
  ('AA',3,1),
  ('AA',11,1),
  ('AA',12,1),
  ('AA',13,1),
  ('AA',14,1),
  ('AA',15,1),
  ('AB',16,1),
  ('AB',17,1),
  ('AB',18,1),
  ('AB',19,1),
  ('AB',20,2),
  ('AB',21,2),
  ('AB',22,2);

I wish to group the data based on ID_User and ID_RingType and for each contiguous range of numbers list the MIN and MAX.

The results should look like:

ID_User | ID_RingType | MIN  | MAX
1       | 'AA'        | 1    | 3
1       | 'AA'        | 11   | 15
1       | 'AB'        | 16   | 19
2       | 'AB'        | 20   | 22

I went through several posts on this topic but was not able to tweak them to fit my data.

Any help would be appreciated.

Since MySQL doesn't support ROW_NUMBER(), You can use a variable to create a group like this.

Sample Data

CREATE TABLE `Rings` (
    ID_RingType CHAR(2),
    Number MEDIUMINT UNSIGNED,
    ID_User INT(11)
);

INSERT INTO `Rings` VALUES
  ('AA',1,1),
  ('AA',2,1),
  ('AA',3,1),
  ('AA',11,1),
  ('AA',12,1),
  ('AA',13,1),
  ('AA',14,1),
  ('AA',15,1),
  ('AB',16,1),
  ('AB',17,1),
  ('AB',18,1),
  ('AB',19,1),
  ('AB',20,2),
  ('AB',21,2),
  ('AB',22,2);

Query

SET @grp = 0;
SET @preNum = 0;

SELECT ID_User,ID_RingType,MIN(Number),MAX(Number) FROM
(
SELECT
ID_RingType,
ID_User,
Number,
@grp := CASE WHEN Number = @preNum + 1   THEN @grp ELSE @grp + 1 END grp,
@preNum := Number
FROM `Rings`
ORDER BY ID_RingType,ID_User,Number
)T
GROUP BY ID_RingType,ID_User,grp

Output

ID_User | ID_RingType | MIN  | MAX
1       | 'AA'        | 1    | 3
1       | 'AA'        | 11   | 15
1       | 'AB'        | 16   | 19
2       | 'AB'        | 20   | 22


How to GRANT the USAGE privilege on SCHEMAS that will be created in the future I need a role to access a schema owned by another role, which can be dropped and re-created in the future. For the first part of my requirement, i.e. for an existing schema, the following would do:
GRANT USAGE ON SCHEMA existing_schema TO my_role;

For the second part of the requirement, i.e. for schemas that don't exist yet but could be created in the future, the ALTER DEFAULT PRIVILEGES command, as documented here, looked promising:
ALTER DEFAULT PRIVILEGES FOR ROLE my_role
  GRANT USAGE ON SCHEMAS TO my_role;

but it doesn't work as expected, i.e. after creating a new schema owned by another user:
CREATE SCHEMA new_schema AUTHORIZATION other_role;

and trying to access it as my_role I get:
my_role=> SELECT * FROM new_schema.some_table;
ERROR:  permission denied for schema new_schema

What am I doing wrong here?
PS I am aware of this answer but modifying system databases, such as template1 is not an option for me since I am neither the owner nor the only user of the database cluster in question.

Your ALTER DEFAULT PRIVILEGES needs to be for the role who will be creating the schema, so it needs to be FOR ROLE other_role.


How to skip prefix column check in multi column index in MySQL? Below is my demo table structure:

CREATE TABLE `table1` (
  `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent',
  `postLocation` smallint(6) NOT NULL,
  `postArea` smallint(6) DEFAULT NULL,
  `postDetails` text,
  KEY `table1_postType_postLocation_postArea_index` (`postType`,`postLocation`,`postArea`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here i have index on column (postType, postLocation, postArea). My postType column value are fixed, it will be 0 or 1.

How can i use index (postType, postLocation, postArea) or skip postType column value and use other two index?

If i include 0, 1 in query is it gonna skip the postType column from index check? or will it check index first for postType column in 1 or 2 than will continue to next column in the index? Below is an example:

select *
from table1 t
where t.postType in (0, 1)
  and t.postLocation = 1
  and t.postArea = 15;

  Please suggest if i need to update my datatype for postType column.

It depends.  Since you are using MySQL 8.0, the index starting with type is probably fine.  I am guessing that the optimizer will be smart enough to

fetch rows with (0,1,15)
Continue fetching, now for rows with (1,1,15).

You can get some clues from EXPLAIN or EXPLAIN FORMAT=JSON.

You can 'prove' that it is (or is not) doing it in that optimal way by this technique:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

If the total of the Handler_read_% values is very close to the number of rows returned, then it probably did it that optimal way.  Else the Handler_read values may be much larger.

I see no PRIMARY KEY on that table.  If that triple is 'unique' then make it the PK.  This will speed it up a little more due to "clustering" of the PK.

If the index is not good enough, rearrange it to have type last.

(Pet peeve:  Drop the constant "post" from column names; it clutters queries.)


Update a column, selected by query Just stumbled upon this stackoverflow question: https://stackoverflow.com/questions/13648898/sql-update-with-row-number. The second answer struck me as odd:

UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
      SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
      FROM DESTINATAIRE_TEMP
      ) x

Here, a column CODE_DEST is updated with the value of ROW_NUMBER(). However, x.CODE_DEST itself is the result of a select query. So, I'm updating the result of a select query. Is this common practice?

It's not common but it can work.  Under certain circumstances (and I'm not sure of the rules) you can update a column from a CTE or subquery.  My guess is that it's very similar to being able to update a view and probably has the same rules.

The times I've done it it's quite a bit faster than joining back to the original table and updating it.


Where to learn advanced SQL coding Where can I learn more advanced SQL programming to improve my ability in SQL?

I have reviewed the database AdventureWorks 2008 but I need more.

Read questions on sites like this one, Stackoverflow or other DBMS forums.

Then try to solve those problems.

Solving real world problems is still the best practice you can have.


Best way to create a login with hashed password? What is the correct way to create a login with a hashed password because .

I've tried the method explained in https://msdn.microsoft.com/en-us/library/ms189751.aspx but it dosn't work.

Query1

CREATE LOGIN administratori WITH hashed_password = '12345'
,DEFAULT_DATABASE = MDAfondation
,DEFAULT_LANGUAGE = english
,CHECK_EXPIRATION = OFF
GO

If somebody could tell me how to write the hashed password part.

Should i do it like this , and if i do it like this when i authenticate (login) how do i tell sql it should hash the password first.

CREATE LOGIN administratori WITH PASSWORD = (HASHBYTES('SHA2_512','12345'))
,DEFAULT_DATABASE = MDAfondation
,DEFAULT_LANGUAGE = english
,CHECK_EXPIRATION = OFF
GO

So please what would be the best way to create a login with a hashed password (preferably hashed with SHA2_512)with a query.And if anyone can tell me where the mistake is at 'Query1' i would be grateful.

The HASHED syntax only indicates that the specified password is already hashed.  SQL Server stores all passwords as hashes, it never stores them in plain-text.

With that out of the way, the syntax for creating a login with a hashed password is:

CREATE LOGIN administratori
WITH PASSWORD = 0x01003BA91A73BB0B9C06A647FC81F3B221EDA8E74AF33C20252F HASHED
    , DEFAULT_DATABASE = MDAfondation
    , DEFAULT_LANGUAGE = english
    , CHECK_EXPIRATION = OFF;

The password is specified as a binary value - this value is the "hashed" version of the password.  The hash above was obtained by the PWDENCRYPT function, like so:

SELECT PWDENCRYPT('P@ssw0rd');

Specifying the password as a hashed value is useful for scripting logins from one server to be deployed on another server when you need to ensure the passwords for each login are precisely the same on the new server.  A starting point for code that generates logins from an existing server might be:

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT N'CREATE LOGIN ' + QUOTENAME(sp.name) + N'
WITH PASSWORD = ' + COALESCE(sys.fn_varbintohexstr(
    CONVERT(VARBINARY(MAX), LOGINPROPERTY(sp.name, 'PasswordHash')
    )), '') + N' HASHED
    , SID = ' + CONVERT(VARCHAR(40), sys.fn_varbintohexstr(sp.sid), 0) + N'
    ' + CASE WHEN sp.default_database_name IS NOT NULL THEN N', DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name) ELSE N'' END + N'
    ' + CASE WHEN sp.default_language_name IS NOT NULL THEN N', DEFAULT_LANGUAGE = ' + sp.default_language_name ELSE N'' END + N'
    ' + CASE WHEN LOGINPROPERTY('SomeLogin', 'DaysUntilExpiration') IS NULL THEN N', CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF' ELSE N'' END + N'
' + CASE WHEN sp.is_disabled = 1 THEN N'ALTER LOGIN ' + sp.name + N' DISABLE;
' ELSE N'' END + N'
'
FROM sys.server_principals sp
WHERE sp.type_desc = N'SQL_LOGIN'
    AND NOT (sp.name LIKE N'##%')
    AND NOT (sp.name = N'sa')
ORDER BY sp.name;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR (@cmd, 0, 1) WITH NOWAIT;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

I'm including that to show how the hashed password is intended to be used.

Creating new logins using a hashed password is not recommended since there is no way for SQL Server to confirm the password matches policies you've set such as minimum password length and complexity.


How to get the top row value in insert of temporary table insert into @Temp([dateTime],Reading)
values (
@startDate,
(select top(1) @dynamicCOLUMN from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))

I want the value of dynamic column instead it returns columns name in select subquery

If there are only a few possible columns, then you could do a separate check for each column name:

if (@dynamicCOLUMN = 'Column1')
begin
    insert into @Temp([dateTime],Reading)
    values (
    @startDate,
    (select top 1 Column1 from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))
end

if (@dynamicCOLUMN = 'Column2')
begin
    insert into @Temp([dateTime],Reading)
    values (
    @startDate,
    (select top 1 Column2 from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))
end

If there are a lot of possible columns, then dynamic queries as suggested by McNets may be more suitable.


MySQL - SELECT from multiple tables by unique attribute, return one row with all values I have the following three tables:

t1:
id | name
---------
1  | joe
2  | jon

t2:
id | street
--------------
1  | "8th St."

t3:
id | postcode
--------------
1  | "OC24 8BB"

How, using MySQL SELECT, can I specify a value for the common unique attribute 'id', and receive a single row combining the values from the three tables, with NULL values if none were found?

For example, if id = 1, I would expect the result:

id | name | street    | postcode
----------------------------------
1  | joe  | "8th St." | "OC24 8BB"

Whereas if id = 2, the following would be returned:

id | name | street | postcode
------------------------------
2  | jon  | NULL   | NULL

My current best attempt at the select is the following:

     SELECT A.id, A.name, B.street FROM t1 as A LEFT JOIN t2 AS B
     WHERE A.id = 1
     UNION ALL
     SELECT A.id, B.postcode FROM t1 as A LEFT JOIN t2 as B
     WHERE A.id = 1;

However this does not return the null values I require, I feel there is a simple solution however I have been looking for a while now and not found anything that has worked for me. Thank you if you can help shine some light on this problem.

You do not need UNION in this case, simple LEFT JOIN

SELECT
   a.id,
   b.street,
   c.postcode
FROM t1 a
LEFT JOIN t2 b ON a.id=b.id
LEFT JOIN t3 c ON a.id=c.id

should work


Best practice regarding concurrency for INSERT into a table with composite primary key? Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / concurrency issues?
Option 1)
First query the table and see if the relationship already exists, if it does just notify the user that made the request.
Option 2)
While inserting, use the WHERE EXISTS syntax (2nd example).
Option 3)
Use Postgres 9.5 Beta for the UPSERT functionality. (I suppose using a Beta version for a school project isn't that bad of an idea?).
What option would be best when it comes to concurrency. I'm not very experienced with SQL (just the standard stuff for CRUD applications). But as far as I know, in the first option, a relationship could be inserted by another user right after querying the junction table but before actually inserting a relation myself. I suppose there won't be any duplicates because I've got a composite primary key in that junction table, but I feel like it's bad practice to just let it make the exception and continue. Furthermore, in my current code, I'd return a 500 http status in this case (because of a db exception), which doesn't really seem right.
I've read I could partially solve the above by 'locking' the database/table, but I don't know if that's the right method.
Finally, I have no idea if there's a race condition when it comes to the 2nd option.

If I wanted to add a user to a team, what's the best option

For just INSERT INTO teams_users ...  and without raising an exception, the answer is option 3: Use Postgres 9.5 Beta for the UPSERT functionality. For a "school project" this is the best option anyway. Your students will want to study the latest version.

INSERT INTO teams_users (team_ID, user_ID)
VALUES (1, 2)
ON CONFLICT ON CONSTRAINT teams_users_pkey DO NOTHING;

Where teams_users_pkey is the actual name of your PK constraint.

This inserts the new row or does nothing if the unique index would raise a duplicate key violation. Designed to be fast and safe against concurrent writes.

Additional question

  Finally, I have no idea if there's a race condition when it comes to
  the 2nd option.

Option 2 being:

  While inserting, use the WHERE EXISTS syntax (2nd example).

That would actually be WHERE NOT EXISTS in your case and yes, there is a race condition. Two parallel transactions could find that a certain combination does not exist yet at virtually the same time and happily try to insert it. The slower one would run into a unique violation and error out. Not much of a problem if your application is prepared for that eventuality.


How does a transaction log backup deal with active log? What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?

Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.
I think it's also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.

Quoted explanations and some key points to help clarify further

1. Transaction Log Backup
A transaction log backup allows you to backup the active part of the
transaction log.  So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed.  After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes.  If a transaction log backup is not taken,
the transaction log will continue to grow.

2. Log Backup Chain
A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is
taken.

3. Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.

Additional Reading

Understanding How Restore and Recovery of Backups Work in SQL
Server


MSSQL - How to log every rpc_completed event into a table? I want to log the Execution Time and Parameters for every stored procedure call on my database.
What I reached by searching is to use extended events as the latest method with minimum performance overhead. My aim is to achieve a similar result as the 'rpc_completed' event of 'extended events', but the problem with using extended events is that their sessions log output into .xe* files and there is no option to choose a database table as far as I read from tutorials/docs.
Can I (by any means), redirect extended events logs to a database table synchronously? I can sacrifice hardware resources for this, but can't pick methods like triggering interval-ed queries to read from *.xe files and insert them into a database table

You can still do this with SQL Server Profiler
when the trace properties window appears check the box - save to table, then supply a server & database plus define a tablename
I don't think extended events offers this functionality in a synchronous manner (you can save to a table once you have collected your data into a ring buffer or file - but you said you didn't want to do this).


Online index rebuild snapshot location and impact of filling index drive My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index.

My question is where does the snapshot index reside?

Our databases have data, log and index file drives. My understanding is the snapshot should reside on the index file drive. Is this correct?

In addition what happens if the index drive runs out of space? Anyone had experience of this? It wont be an issue but I would like to know!

Version Info: SQL Server 2005 9.00.4266.00(x64) EE

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index.

Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work:

  A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency.

With this correction, the rest of the question is moot. Row-versioning does not create a copy of the data until data is modified, and then the copy resides in tempdb. In other words, as you continue to modify the original index while OIB is running the row-versioning will have to preserve the pre-update image of the updated rows in tempdb.

@Shanky is right about SORT_IN_TEMPDB, but that refers to the index builder, a different stage, unrelated to the original 'snapshot', and an option that is independent of the 'online' nature of the OIB.

Obviously during the OIB you will slowly build up a copy of the data (the new index). This must be in the same location as the original index (including filegroups for partitions etc) as it has to be a valid replacement of the original index when the OIB is done.


Data sync across Local and Cloud Database There is a Central Master DB(on Cloud) and there are multiple Locations which have their own DB(lets call the latter - Local DB).

The Local DBs are Single Tenant and contain data for that particular Location alone. All data from Local DBs need to be synced with Central Master DB, i.e, Central Master DB behaves like a Multi-tenant DB with data from all locations.

The web-application load balances between the Central Master DB or Local DB based on availability. Preference is always given to Central Master DB when connectivity is there; if the connectivity to Cloud is not present, the data needs to be written to Local DB which then later needs to be synced with the Central Master DB.

What kind of Replication strategy should be followed so that Master syncs only the data relevant for that LocalDB? In someway, this is similar to ‘filtered replication in a master-master replication setup’ - but am not able to find much about this; and also, am not sure if this is the right approach or not. Or is, replication not a possibility, and only a custom sync needs to be done?

Also, not sure which of MySQL 5.7 and Postgres v11 is better for this kind of use case - this question arises as this use-case is primary and we can choose between either of the databases. Only RDBMS and no NOSQLs are considered.

If the "Local DBs" were only read slaves then this would be feasible using replication filters. https://dev.mysql.com/doc/refman/7.0/en/change-replication-filter.html

However since you have some sort of master master requirements (the need to be able to write to both Local DBs and the Central Master) this will get messy quickly. Reason being MySQL is not meant for master master replication out of the box let alone master master with bi-directional replica filter demands. As such, there are two possible solutions I can think of.

1.) Split the the Central Master into multiple databases which are location specific, reflecting their given Local DB partner. Each partner pair should then become a Galera "cluster" consisting of both nodes plus an arbitrator.

2.) Have the entire topology you are describing be a Galera cluster and don't discriminate between location based data, i.e all servers have all data. This solution would obviously greatly reduce complexity but at the cost of increased resource requirements.

3.) If you prefer to use Postgres, you can experiment with some variant of the 2 above mentioned solutions using https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/
But it seems at this time that multimaster and logical replication aren't coexisting.

To answer your other questions:

-I would try to achieve some sort of replication instead of a custom sync which would be difficult to implement especially if your data set is large. Also a custom sync solution will result in data inconsistency the majority of the time.

-In my opinion MySQL has more mature replication facilities available relative to your particular challenge.


mysql access denied from localhost I've a very weird problem on mysql/Aurora 1.14 I have installed on a Linux EC2 instance on AWS.

I have an user able to connect remotely to the instance but not locally.

select user,host from mysql.user;
user | host
admin| %

I'm able to connect with that user from outside

mysql -u admin -pxxxx -h ip

but when I'm inside the machine

mysql -u admin -pxxx
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

I've tried several other things (with or without sudo)

mysql -u admin -pxxx -h localhost
mysql -u admin -pxxx -h 127.0.0.1
mysql -u admin -pxxx --proto=TCP

I've also tried to create another user

create user 'localtest'@'localhost' identified by 'pwd18';
grant all on mydb.* to 'localtest'@'localhost';
flush privileges;

mysql -u localtest -ppwd18 -h localhost
ERROR 1045 (28000): Access denied for user 'localtest'@'localhost' (using password: YES)

Other weird things is I don't have an user "root" in mysql.user.

And if I try from remote, with user admin to give grant all on all dbs to localtest I get:

GRANT ALL PRIVILEGES ON *.* TO 'localtest'@'localhost'
Error Code: 1045. Access denied for user 'admin'@'%' (using password: YES)

No more ideas.
Logs is not very useful, just says "access denied"

As requested:

SELECT USER()
admin@xx.xx.xxx.x   admin@%

SHOW GRANTS FOR 'admin'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, LOAD FROM S3, SELECT INTO S3 ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*xxxxxx' WITH GRANT OPTION

I also gave explicitly all above grants to user 'localtest'@'localhost', but nothing changed

% deliberately does not include localhost.  This decision was possibly made to emphasize the difference between the relatively secure local connection versus coming in from the World Wild Web.  Or perhaps because it implies a socket instead of TCP/IP.  (There is not much performance difference.)

So, either

Use the IP (or hostname) of the machine, or
GRANT ... TO admin@localhost ...;


Why does DELETE leave a lingering effect on performance? At the end is a test script for comparing the performance between a @table variable and a #temp table.  I think I've set it up correctly - the performance timings are taken outside of the DELETE/TRUNCATE commands.  The results that I am getting are as follows (times in milliseconds).

@Table Variable  #Temp (delete)  #Temp (truncate)
---------------  --------------  ----------------
5723             5180            5506
15636            14746           7800
14506            14300           5583
14030            15460           5386
16706            16186           5360

Just to make sure I'm sane, this shows that CURRENT_TIMESTAMP (aka GetDate()) is taken at the time of the statement, not of the batch, so there should be no interaction between TRUNCATE/DELETE with the SET @StartTime = CURRENT_TIMESTAMP statement.

select current_timestamp
waitfor delay '00:00:04'
select current_timestamp

-----------------------
2012-10-21 11:29:20.290

-----------------------
2012-10-21 11:29:24.290

It's quite consistent in the jump between the first run and the subsequent runs when DELETE is used to clear the table.  What am I missing in my understanding of DELETE?  I have repeated this many times, swapped the order, sized tempdb to not require growth etc.

CREATE TABLE #values (
  id int identity primary key, -- will be clustered
  name varchar(100) null,
  number int null,
  type char(3) not null,
  low int null,
  high int null,
  status smallint not null
);
GO
SET NOCOUNT ON;

DECLARE @values TABLE (
  id int identity primary key clustered,
  name varchar(100) null,
  number int null,
  type char(3) not null,
  low int null,
  high int null,
  status smallint not null
);
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    )
DECLARE  @StartTime DATETIME,  @i INT = 1;
WHILE (@i <= 5)
  BEGIN
    DELETE @values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
    /****************** measured process ***********************/

    INSERT @values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/
    INSERT @ExecutionTime
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
    SET @i +=  1
  END -- WHILE

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime
GO

-- Temporary table
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    )
DECLARE  @StartTime DATETIME,  @i INT = 1;
WHILE (@i <= 5)
  BEGIN
    delete #values;
    -- TRUNCATE TABLE #values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
    /****************** measured process ***********************/

    INSERT #values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/
    INSERT @ExecutionTime
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
    SET @i +=  1
  END -- WHILE

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime
GO

DROP TABLE  #values
SET NOCOUNT OFF;

This difference only seems to apply when the object is a B+tree. When removing the primary key on the table variable so it is a heap I got the following results

2560
2120
2080
2130
2140

But with the PK I found a similar pattern in my tests as well with typical results below.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2670 |   2683 |    9603 |              9703 |
|   6823 |   6840 |    9723 |              9790 |
|   6813 |   6816 |    9626 |              9703 |
|   6883 |   6816 |    9600 |              9716 |
|   6840 |   6856 |    9610 |              9673 |
+--------+--------+---------+-------------------+

My theory is that there there is some optimisation available when doing bulk inserts to local temporary B+ trees that only applies when it does not already have any pages allocated.

I base this on the following observations.

When running various versions of your test code I only saw this pattern  with @table_variables and #temp tables. Not permanent tables in tempdb nor ## tables.
In order to get the slower performance it is not necessary to have previously added and removed a large amount of rows from the table. Simply adding a single row and leaving it in there is sufficient.
TRUNCATE deallocates all pages from the table. DELETE will not cause the last page in the table to be deallocated.
Using the VS 2012 profiler shows that in the faster case SQL Server uses a different code path. 36% of the time is spent in sqlmin.dll!RowsetBulk::InsertRow vs 61% of the time spent in sqlmin.dll!RowsetNewSS::InsertRow for the slower case.

Running

SELECT *
FROM sys.dm_db_index_physical_stats(2,OBJECT_ID('tempdb..#values'),1,NULL, 'DETAILED')

after the delete returns

+-------------+------------+--------------+--------------------+
| index_level | page_count | record_count | ghost_record_count |
+-------------+------------+--------------+--------------------+
|           0 |          1 |            0 |                  1 |
|           1 |          1 |            1 |                  0 |
|           2 |          1 |            1 |                  0 |
+-------------+------------+--------------+--------------------+

I found that it was possible to reduce the time discrepancy somewhat by enabling trace flag 610.

This had the effect of reducing the quantity of logging substantially for the subsequent inserts (down from 350 MB to 103 MB as it no longer logs the individual inserted row values) but this had only a minor improvement in timings for the 2nd and subsequent @table , #table cases and the gap still remains. The trace flag improved the general performance of inserts to the other two table types significantly.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2663 |   2670 |    5403 |              5426 |
|   5390 |   5396 |    5410 |              5403 |
|   5373 |   5390 |    5410 |              5403 |
|   5393 |   5410 |    5406 |              5433 |
|   5386 |   5396 |    5390 |              5420 |
+--------+--------+---------+-------------------+

From looking in the transaction log I noticed that the initial inserts against empty local temporary tables seem even more minimally logged (at 96 MB).

Notably these faster inserts had only 657 transactions (LOP_BEGIN_XACT /LOP_COMMIT_XACT pairs) compared with over 10,000 in the slower cases. In particular LOP_FORMAT_PAGE operations seem much reduced. The slower cases have a transaction log entry for this for each page in the table (about 10,270) compared with only 4 such entries in the fast case.

The log used in all three cases was as follows (I have deleted the log records for updates to system base tables to reduce the amount of text but they are still included in the totals)

Logging first insert against @table_var (96.5 MB)

+-----------------------+----------+----------------------------------------------+---------------+---------+
|       Operation       | Context  |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-----------------------+----------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_XACT        | LCX_NULL | NULL                                         |         83876 |     658 |
| LOP_COMMIT_XACT       | LCX_NULL | NULL                                         |         34164 |     657 |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL                                         |           120 |       3 |
| LOP_FORMAT_PAGE       | LCX_HEAP | dbo.#531856C7                                |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | dbo.#531856C7                                |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | Unknown Alloc Unit                           |            84 |       1 |
| LOP_HOBT_DDL          | LCX_NULL | NULL                                         |           216 |       6 |
| LOP_HOBT_DELTA        | LCX_NULL | NULL                                         |           320 |       5 |
| LOP_IDENT_NEWVAL      | LCX_NULL | NULL                                         |     100240000 | 2506000 |
| LOP_INSERT_ROWS       | LCX_HEAP | dbo.#531856C7                                |            72 |       1 |
| LOP_MODIFY_ROW        | LCX_IAM  | dbo.#531856C7                                |            88 |       1 |
| LOP_MODIFY_ROW        | LCX_PFS  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        158592 |    1848 |
| LOP_MODIFY_ROW        | LCX_PFS  | dbo.#531856C7                                |            80 |       1 |
| LOP_MODIFY_ROW        | LCX_PFS  | Unknown Alloc Unit                           |        216016 |    2455 |
| LOP_SET_BITS          | LCX_GAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         84360 |    1406 |
| LOP_SET_BITS          | LCX_GAM  | Unknown Alloc Unit                           |        147120 |    2452 |
| LOP_SET_BITS          | LCX_IAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         84360 |    1406 |
| LOP_SET_BITS          | LCX_IAM  | Unknown Alloc Unit                           |        147120 |    2452 |
| Total                 | NULL     | NULL                                         |     101209792 | 2519475 |
+-----------------------+----------+----------------------------------------------+---------------+---------+

Logging subsequent inserts TF 610 off (350 MB)

+-----------------------+--------------------+----------------------------------------------+---------------+---------+
|       Operation       |      Context       |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-----------------------+--------------------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_CKPT        | LCX_NULL           | NULL                                         |            96 |       1 |
| LOP_BEGIN_XACT        | LCX_NULL           | NULL                                         |       1520696 |   12521 |
| LOP_COMMIT_XACT       | LCX_NULL           | NULL                                         |        651040 |   12520 |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL           | NULL                                         |            40 |       1 |
| LOP_DELETE_SPLIT      | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          2160 |      36 |
| LOP_END_CKPT          | LCX_NULL           | NULL                                         |           136 |       1 |
| LOP_FORMAT_PAGE       | LCX_HEAP           | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        859236 |   10229 |
| LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit                           |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          3108 |      37 |
| LOP_HOBT_DDL          | LCX_NULL           | NULL                                         |           648 |      18 |
| LOP_HOBT_DELTA        | LCX_NULL           | NULL                                         |        657088 |   10267 |
| LOP_IDENT_NEWVAL      | LCX_NULL           | NULL                                         |     100239960 | 2505999 |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |     258628000 | 2506000 |
| LOP_INSERT_ROWS       | LCX_HEAP           | dbo.#531856C7                                |            72 |       1 |
| LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |       1042776 |   10302 |
| LOP_MODIFY_HEADER     | LCX_HEAP           | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        859236 |   10229 |
| LOP_MODIFY_HEADER     | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          3192 |      38 |
| LOP_MODIFY_ROW        | LCX_IAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |           704 |       8 |
| LOP_MODIFY_ROW        | LCX_PFS            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        934264 |   11550 |
| LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit                           |        783984 |    8909 |
| LOP_SET_BITS          | LCX_GAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         76980 |    1283 |
| LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit                           |        534480 |    8908 |
| LOP_SET_BITS          | LCX_IAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         76980 |    1283 |
| LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit                           |        534480 |    8908 |
| LOP_SHRINK_NOOP       | LCX_NULL           | NULL                                         |            32 |       1 |
| LOP_XACT_CKPT         | LCX_NULL           | NULL                                         |            92 |       1 |
| Total                 | NULL               | NULL                                         |     367438748 | 5119297 |
+-----------------------+--------------------+----------------------------------------------+---------------+---------+

Logging subsequent inserts TF 610 on (103 MB)

+-------------------------+-------------------------+----------------------------------------------+---------------+---------+
|        Operation        |         Context         |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_CKPT          | LCX_NULL                | NULL                                         |           192 |       2 |
| LOP_BEGIN_XACT          | LCX_NULL                | NULL                                         |       1339796 |   11099 |
| LOP_BULK_EXT_ALLOCATION | LCX_NULL                | NULL                                         |         20616 |     162 |
| LOP_COMMIT_XACT         | LCX_NULL                | NULL                                         |        577096 |   11098 |
| LOP_CREATE_ALLOCCHAIN   | LCX_NULL                | NULL                                         |            40 |       1 |
| LOP_DELETE_SPLIT        | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          2160 |      36 |
| LOP_END_CKPT            | LCX_NULL                | NULL                                         |           272 |       2 |
| LOP_FORMAT_PAGE         | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        863520 |   10280 |
| LOP_FORMAT_PAGE         | LCX_IAM                 | Unknown Alloc Unit                           |            84 |       1 |
| LOP_FORMAT_PAGE         | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          3108 |      37 |
| LOP_HOBT_DELTA          | LCX_NULL                | NULL                                         |        666496 |   10414 |
| LOP_IDENT_NEWVAL        | LCX_NULL                | NULL                                         |     100239960 | 2505999 |
| LOP_INSERT_ROWS         | LCX_CLUSTERED           | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         23544 |     218 |
| LOP_INSERT_ROWS         | LCX_HEAP                | dbo.#719CDDE7                                |            72 |       1 |
| LOP_INSERT_ROWS         | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |       1042776 |   10302 |
| LOP_MODIFY_HEADER       | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        780216 |   10266 |
| LOP_MODIFY_HEADER       | LCX_HEAP                | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |       1718472 |   20458 |
| LOP_MODIFY_HEADER       | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          3192 |      38 |
| LOP_MODIFY_ROW          | LCX_IAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |           704 |       8 |
| LOP_MODIFY_ROW          | LCX_PFS                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        114832 |    1307 |
| LOP_MODIFY_ROW          | LCX_PFS                 | Unknown Alloc Unit                           |        231696 |    2633 |
| LOP_RANGE_INSERT        | LCX_NULL                | NULL                                         |            48 |       1 |
| LOP_SET_BITS            | LCX_GAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         77100 |    1285 |
| LOP_SET_BITS            | LCX_GAM                 | Unknown Alloc Unit                           |        157920 |    2632 |
| LOP_SET_BITS            | LCX_IAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         77100 |    1285 |
| LOP_SET_BITS            | LCX_IAM                 | Unknown Alloc Unit                           |        157920 |    2632 |
| LOP_XACT_CKPT           | LCX_NULL                | NULL                                         |            92 |       1 |
| Total                   | NULL                    | NULL                                         |     108102960 | 2602218 |
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+


How to fix SQL Agent Job using Windows Security after password change I have some sql agent jobs that are using Windows NT Integrated Security to run (as specified in the Maintenance Plan's Manage Connections window)...eg. Translog backups.
While logged in as this account, I changed its password. Now the maintenance job is failing.
reason: Error authenticating proxy DOMAIN\Account, system error: The user name or password is incorrect
How do you fix this?
What I've tried:

Logging out of the server and back on
Per this article, Changed the connection to use SQL Authentication and then restarted SQL Agent...it still fails, but the error still is the same showing the same DOMAIN/Account that failed, so it is still using the Windows authentication instead of the new SQL login provided.

Figured it out.
The job step is also using a Proxy account, which is the same account. So you need to change the proxy account credentials as well.


How can I speed up a stored procedure? Here is an interview question I just had. I would like to know what a good answer to this would have been and what the different options would be.

  We have a stored procedure that is taking 26 seconds to return
  information that is grabbed from 5 tables. Our server times out after
  25 seconds. How can you solve this problem?

This is not verbatim but it is close to the question that was asked. I had no clue, and I don't even think we went into that kind of detail in our db class. All I could think of telling them is to make sure that the tables are indexed and I don't even know if that is right.

Here's an example of how this could look on creation:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
SELECT * FROM orders o
    JOIN state s ON s.stateID = o.stateID
    JOIN customers c ON c.customerID = o.customerID
    JOIN items i ON i.itemID = o.itemID
    JOIN parts p ON p.partListID = o.partListID
WHERE c.customerID = @CustID
END

Once again this would be taking slightly more time than the timeout time allows.

What are my options for speeding up this stored procedure syntactically?
What are the different types of metadata that might help solve the problem?
What practices should be avoided that are seen in the procedure above?
(As a non-required aside question) Would it be acceptable to tell an interviewer that I would ask the real-life question on Stack Overflow?

Amazingly broad question but a couple of simple answers.

Indexing like you suggested.  Make sure the appropriate indexes exist and if the need is great enough make sure they are all covering.
Get rid of the *.  Specify the columns you need.  If you are pulling across 100 columns over a million rows that's going to be a LOT of data.  If you only need 3 columns only specify 3 columns.
Change the timeout.  This sounds hokey but honestly sometimes it really is the answer.  SQL Server (for example) doesn't actually have a timeout so the problem is on the connection side.  Have them increase the timeout to 30 seconds (assuming this is an acceptable amount of time, and it frequently won't be).
Make sure it's the DBMS's fault.  It's very possible you are having a problem on the connection side that's causing it to take 5-10 seconds just to connect.  Fix that and you are well within your time.


Azure Sql data warehouse - is there any way to see the true query plan? Azure SQL DWH / PDW have the EXPLAIN keyword to show the parallel query plan. This is really useful for seeing the data movement operations, however I was wondering if there was an equivalent of the traditional sql server query plan. I'm working on the assumption that just because there isn't any data movement, it doesn't necessarily mean that query is well optimised.

Traditional SQL Server query plan is not available yet.  You can use DBCC PWD_SHOWEXECUTIONPLAN to see estimated execution plan. For actual execution plan you can get a D-SQL plan using Visual Studio. Grant Fritchey explains in details.


Here's a stab at this: see fiddle at http://sqlfiddle.com/#!9/1fc60/1.  My algorithm in general to take all the start/end values for all clients, sort those numbers and make each adjacent pair of values into an interval.  For n clients, you have 2n-1 intervals (in worst case). Then, evaluate each of those intervals to see how many clients' IP ranges they fall into.  If more than 1, then you found a conflicting IP range between two clients.  It does works, but as you get bigger data sets, it gets considerably slower.  The algorithm is Polynomial in time complexity.  On modest hardware, I was able to process 16k clients for conflicts in about 6 minutes.

I think there's a much better algorithm that might take logarithmic time (good), or even linear time (way better), but this algo would be pretty tough to do in MySQL or any RDBMS.  It would be much easier in an OO 3GL like Java or .NET.


How to go about modeling the main object of relationship? I'm using MySQL and MyISAM tables.

I have a bunch of objects that I want to group together

1 object belongs to 1 and only 1 group
1 group can have many objects
1 group has 1 required main object that i will choose
manually

So far, I’ve build a model that I believe is the right one.

But I don’t know how to go about modeling the main object in the group. What is the perfect solution?

group_object
----------------
group_object_id (PRIMARY KEY)
name

object
----------------
object_id (PRIMARY KEY)
group_object_id (FOREIGN KEY)
name

UPDATED MY QUESTION.. see bold

Since the tables use the MyISAM storage engine, I have two suggestions.

SUGGESTION #1 : Use Order of Insertion

The simplest way to do that would be arbitrarily insert the main object first for the given group.

Going forward, you would run queries like these:

Main object_id for every group

SELECT group_object_id,MIN(object_id) main_object_id
FROM object GROUP BY group_object_id;

Main object for every group by name

SELECT DISTINCT B.name GroupName,C.name ObjectName FROM
(SELECT group_object_id,MIN(object_id) main_object_id
FROM object GROUP BY group_object_id) A
INNER JOIN object B ON A.main_object_id = B.object_id
INNER JOIN group_object C ON B.group_object_id = C.group_object_id;

Epilogue

To ensure the first rule, 1 object belongs to 1 and only 1 group, add this index

ALTER TABLE object ADD UNIQUE INDEX (group_object_id,object_id);

Having a unique index on those two columns along with object_id definitely prevents insertion of an object_id twice in a group. This unique would speed up GROUP BY queries like the ones I mentioned. It would also speed up the collection of the objects in a group like the following:

SET @given_group_id = 53;
SELECT object_id,name FROM object
WHERE group_object_id = @given_group_id;

and

SET @given_group_id = 53;
SELECT A.name GroupName,GROUP_CONCAT(B.Name) Objects
FROM group_object A INNER JOIN object B
ON A.group_object_id = B.group_object_id
WHERE A.group_object_id = @given_group_id;

SUGGESTION #2 : Use two auto_increments within the same table

All queries in SUGGESTION #1 still apply, but the design is a little different.

Make the design the object table as follows:

object
----------------
object_id
group_object_id
PRIMARY KEY (group_object_id,object_id)
name

MyISAM would handle the incremental values of all object_id per group_object_id. At present, only the MyISAM storage engine has this capability. I have discussed this before:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Apr 30, 2013 : Issues converting MyISAM table to InnoDB (auto column issue)

Give it a Try !!!

UPDATE 2013-08-28 02:05 EDT

Remember I said

  The simplest way to do that would be arbitrarily insert the main object first for the given group.

EXAMPLE

Let's say you ran the following to create the tables

use test
drop table if exists group_object;
drop table if exists object;
create table group_object
(
    group_object_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20),
    PRIMARY KEY (group_object_id)
) ENGINE=MyISAM;
create table object
(
    object_id INT NOT NULL AUTO_INCREMENT,
    group_object_id INT NOT NULL,
    name VARCHAR(20),
    PRIMARY KEY (group_object_id,object_id)
) ENGINE=MyISAM;

and load the tables with

INSERT INTO group_object (name) VALUES ('GROUP1'),('GROUP2'),('GROUP3');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP1';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_A'),
(@GivenGroupObjectID,'OBJ_B'),
(@GivenGroupObjectID,'OBJ_C');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP2';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_X'),
(@GivenGroupObjectID,'OBJ_Y'),
(@GivenGroupObjectID,'OBJ_Z');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP3';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_1'),
(@GivenGroupObjectID,'OBJ_2'),
(@GivenGroupObjectID,'OBJ_3');

You will have this data:

mysql> select * from group_object;
+-----------------+--------+
| group_object_id | name   |
+-----------------+--------+
|               1 | GROUP1 |
|               2 | GROUP2 |
|               3 | GROUP3 |
+-----------------+--------+
3 rows in set (0.00 sec)

mysql> select * from object;
+-----------+-----------------+-------+
| object_id | group_object_id | name  |
+-----------+-----------------+-------+
|         1 |               1 | OBJ_A |
|         2 |               1 | OBJ_B |
|         3 |               1 | OBJ_C |
|         1 |               2 | OBJ_X |
|         2 |               2 | OBJ_Y |
|         3 |               2 | OBJ_Z |
|         1 |               3 | OBJ_1 |
|         2 |               3 | OBJ_2 |
|         3 |               3 | OBJ_3 |
+-----------+-----------------+-------+
9 rows in set (0.00 sec)

mysql>

If you run my queries, note how you get the main object from each group

mysql> SELECT group_object_id,MIN(object_id) main_object_id
    -> FROM object GROUP BY group_object_id;
+-----------------+----------------+
| group_object_id | main_object_id |
+-----------------+----------------+
|               1 |              1 |
|               2 |              1 |
|               3 |              1 |
+-----------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT B.name GroupName,C.name ObjectName FROM
    -> (SELECT group_object_id,MIN(object_id) main_object_id
    -> FROM object GROUP BY group_object_id) A
    -> INNER JOIN object B ON A.main_object_id = B.object_id
    -> INNER JOIN group_object C ON B.group_object_id = C.group_object_id;
+-----------+------------+
| GroupName | ObjectName |
+-----------+------------+
| OBJ_A     | GROUP1     |
| OBJ_X     | GROUP2     |
| OBJ_1     | GROUP3     |
+-----------+------------+
3 rows in set (0.00 sec)

mysql>

Note how the main inserted object for each group has the minimum object_id for that group.

When you apply the rule I first mentioned, simply look for the minimum object_id for any given group_object_id from the object table.

YOUR LAST EDIT

Adding a flag

If you already have data in the object table and want to assign a random object as the main object, you could add a flag called main as INT NOT NULL DEFAULT 0 to the object table. Assuming responsibility, you would have to check to see, upon insertion, if a group already has a main object. If so, you would update the main flag of the previous main object to 0. Then, you could update another object's main to 1.

Rearranging to change main object

If you try adhering to my rule, first insertion is main, here is what you can do so that you can choose which object becomes first in the group:

EXAMPLE

Let's say from my example, that you want take OBJ_Z in GROUP2 and make it the new main object. You would do this:

SELECT group_object_id,object_id
INTO @GivenGroupID,@GivenObjectID
FROM object WHERE name = 'OBJ_Z';
DROP TABLE IF EXISTS objectnew;
CREATE TABLE objectnew LIKE object;
--
--
INSERT INTO objectnew (group_object_id,name)
SELECT @GivenGroupID,name FROM object
WHERE group_object_id = @GivenGroupID
AND object_id = @GivenObjectID;
INSERT INTO objectnew (group_object_id,name)
SELECT @GivenGroupID,name FROM object
WHERE group_object_id = @GivenGroupID
AND object_id <> @GivenObjectID;
--
--
DELETE FROM object WHERE group_object_id = @GivenGroupID;
INSERT INTO object SELECT * FROM objectnew ORDER BY object_id;
DROP TABLE IF EXISTS objectnew;
SELECT * FROM object ORDER BY group_object_id,object_id;

If you run this code, here is the new output

mysql> SELECT * FROM object ORDER BY group_object_id,object_id;
+-----------+-----------------+-------+
| object_id | group_object_id | name  |
+-----------+-----------------+-------+
|         1 |               1 | OBJ_A |
|         2 |               1 | OBJ_B |
|         3 |               1 | OBJ_C |
|         1 |               2 | OBJ_Z |
|         2 |               2 | OBJ_X |
|         3 |               2 | OBJ_Y |
|         1 |               3 | OBJ_1 |
|         2 |               3 | OBJ_2 |
|         3 |               3 | OBJ_3 |
+-----------+-----------------+-------+
9 rows in set (0.00 sec)

mysql>

Note that OBJ_Z is the main object because it is the first in the group. Simply query for the name of the object you want to be main in the first line and then run all the code. I have tried repeatedly, and it works.

Give it a Try !!!


Prevent SQL Server publisher from running out of disk space I have an issue with transactional replication which leads to running out of disk space.
The scenario is following.

Database A is replicated to another computer via local distributor with command retention set to 24 hours (customer requirement).
Subscriber computer gets disconnected for a longer time.
After few hours of high load undstributed commands are piling up on publisher computer causing lack of disk space.

At first I tried to limit distribution db size, but after it reached max size, the ldf file for database A is swelling.
I wouldn't want to set limits on ldf file since it can cause database A to stop functioning.

Is there any approach I could choose to prevent this situation? I don't care about replication failure, my primary concern is to keep the database available for clients.
The perfect option would be an automatic subcription expiration if reached given number of transactions or used given disk space.

Basically the problem is your subscriber going offline.  If it remained online then the replicated data would be committed on the subscriber and there would be no problem.

The effect on transactional replication is described in the following link:
https://technet.microsoft.com/en-us/library/ms151254(v=sql.105).aspx

  For each database that will be published using transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database might require more space than the log of an identical unpublished database, because the log records are not truncated until they have been moved to the distribution database.

The simplest answer is to get more disk space to support the potential log size.

EDIT: Clarification from your reply: The subscriber gets disconnected for a long period due to a failure of some sort.

If disk space cannot be made available to handle the unpublished data, then you basically need to delete the subscription.  Once the outage is resolved, then you can restore and re-establish the replication subscription.


How to perform query operation on another query result How to get approved , unapproved , deleted count from the below result using sql query.

SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher`
    WHERE company_id = 1 GROUP BY approved,deleted_at;

I'm attaching the above-queried result as an image.

  Desired output: Approved:32 not_approved:4 deleted:7

SELECT SUM(CASE WHEN (approved=1) AND (deleted_at IS NULL) THEN `count` END) approved,
       SUM(CASE WHEN (approved=0) AND (deleted_at IS NULL) THEN `count` END) not_approved,
       SUM(CASE WHEN deleted_at IS NOT NULL THEN `count` END) deleted
FROM (your subquery) source


Can Postgres timestamp be used to implement client side caching? If I have a pool of connections in a multi-threaded client sending out requests to the database server, can I rely on transaction or statement level timestamps to determine which data is most current?
For example, let's say I have a pair of transactions T1 and T2 which both return the same row which is updated in some way. Suppose T1 starts first.  Is it possible for T2 to return stale data or complete earlier despite T1's timestamp being earlier?  For instance, if T1 is slow and expensive, can it finish later and/or return data that is somehow more current than the data returned by T2?
Specific use case: Let's say we have a web server, a database server, and two different user agents connected to the web server.  Let's say each user agent submits a mutation "simultaneously", and the web server wants to update the cache accordingly.  How can we cache the correct result on the web server?  By "correct," I mean the final result of these two mutations instead of the intermediate result.

I assume that your problem is that when you see a row with a certain timestamp in the table, you want to to be certain that a later query cannot find a new row with a smaller timestamp.
There is no good way to guarantee that unless you serialize processing, which would be bad for performance.
You might use a workaround: if you know that none of your transactions can take longer than 0.1 seconds, consider rows with a timestamp later than current_timestamp - INTERVAL '1 second' as not yet reliable.  This is a heuristic that can go wrong (I/O overload might make your transactions slower than you think), but it should work out most of the time.


MySQL Database Archive We have 300 databases in our environment mixed of MyISAM and Innodb(innodb_file_per_table has been enabled) tables.Using MySQL 5.5.19 version. I

we are planning archive inactive database.Is there any way we can check what databasese are inactive?

I have checked at the OS level file modification timestamp,but as we have so many database its taking time.Please let me know is there anyway we can check.

Note: Timestamp column is not present in any of tables

(A non-answer)

There is essentially no way.

atime should be turned off, else performance will be quite bad.

Just because you have not used a database in the last week does not mean that you won't use it next week.


MySQL - Rows to Columns I have a table with 3 columns, participant_id, question, answer. for each participant, there will be five question.

How do fetch a report like against each participant each question as a column header and answer as value. So in report for each participant there will be one entry in report?

expected output:
group using participant_id and each question should be the column header and custtusponding answer will be the column value.

This is similar to pivot table , Try This

create view pivot_questions as (
  select
    questions.*,
    case when question = "question1" then itemvalue end as question1,
    case when itemname = "question2" then itemvalue end as question2,
    case when itemname = "question3" then itemvalue end as question3,
    case when itemname = "question4" then itemvalue end as question4,
    case when itemname = "question5" then itemvalue end as question5
  from questions
);

select * from pivot_questions;


TNS-01190: The user is not authorized to execute the requested listener command Oracle 11g Release 2
Oracle Linux Enterprise 5.10

[oracle@dub-ImrORA3 scripts]$ id oracle
uid=502(oracle) gid=502(oinstall) groups=502(oinstall),503(dba),504(oper)           context=user_u:system_r:unconfined_t

We recently changed the oracle id to 502 and the group oinstall to 502.

Everything under the $ORACLE_HOME/bin is owned by oracle:oinstall

Since this change we can only connect locally to the database using IPC. When we try to connect remotely using TCP we the error => ORA-12537: TNS:connection closed

When the oracle user tries to stop the LISTENER we get the TNS-01190 error.

[oracle@dub-ImrORA3 bin]$ ls -al lsnrctl
-rwxr-x--x 1 oracle oinstall 153535 Nov 26 09:45 lsnrctl

Is this an ownership/permission issue? How to resolve?

Yes, this is an issue with owner and group of lot's of files in oracle home. Most files are of oracle:oinstall but not all. Some are owned by root and some owned by nobody.
A safer solution for this would be
find -user {old_uid} |xargs chown {new_uid}
find -group {old_gid}|xargs chgrp {new_gid}

and yes, this should run as root.


Mongodb tree structure questions and answers I'm looking to build a question answer survey system where some questions will be based on the answer of the parent question. The hierarchy level of the questions can go any number of depth based on the questions. The questions and answers will be like the diagram shown here.

I'm looking to build this in mongodb. I have checked the tree model in mongodb and I'm clueless on how to structure it as the next set of questions are depends on the answers. The questions should show based on what answer the user have selected and some answers may have multiple question sets below that as well (E.g.: Ans B).

Can someone help me on the mongodb collection structure and how can I query the next set of questions and answers based on the previous answer given by the user?
Thanks in advance.

If the whole survey is not too big and if it will not grow, I would consider putting all the questions and answers inside one document.

So one document of that collection would look like

{
  "survey": {
    "Question A": [
      {
        "Ans A": [
          {
            "Question C": [...]
          }
        ]
      },
      {
        "Ans B": [
          {
            "Question D": [...],
            "Question E": [...]
          }
        ]
      }
    ]
  }
}

If it is big, and could grow in future, I would consider putting answer and question ids as key-value pair like following.

{
  "title": "Question A",
  "answers": [
    {
      "Ans A": [
        "Question C's Id"
      ]
    },
    {
      "Ans B": [
        "Question D's Id",
        "Question E's Id"
      ]
    }
  ]
}

I wouldn't normalize to have both question and answer collection, because it would take too much time to join each other.


Full-Text Search against serial numbers? Using Dynamics 365 with MSSQL. There is an option to enable Full Text Search, however once it is enabled we cannot search by partial serial numbers.

For instance, KN567228 shows in the full text catalog but trying to search for 7228 will return no results. The Stoplist is empty, so that isn't the cause. This is a unique situation for Full-Text Search because there really isn't a thesaurus or wordbreaker that could break down serial numbers in a meaningful way. But I thought the search would work for partial text (like searching for 7228 in KN567228).

Should I just not be using Full-Text Search in this scenario?

Full-Text Search would be able to efficiently Search for a word with a prefix (Prefix Term) or Search for a specific word or phrase (Simple Term), but not a suffix as you described. You can achieve what you need using LIKE.

As you can see in Eugene Elutin's answer for a Using Full-Text Search thread:

  partial and suffix match in T-SQL can be only done using LIKE, which
  will do full table scan to find relevant rows.

Here's one way of doing the search you described

  Full-text search only supports prefix search. In some circumstances,
  reversing the string, indexing that, and then searching for the
  reverse of the suffix can be made to work reasonably well.
  Fundamentally, though, FTS is not designed for the purpose you
  require.

But that approach would fail to find this other serial KN5672281 when trying to search for 7228.


Oracle: Not able to create tablespace from stored procedure I am trying to create tablespace from stored procedure.

create or replace procedure testprc
is
query_string VARCHAR2(4000);
tbs_name VARCHAR2(20):='test77';
begin
dbms_output.put_line('test..');

query_string := 'CREATE BIGFILE TABLESPACE '|| tbs_name || ' DATAFILE '''|| tbs_name || '.dat'' SIZE 1G REUSE AUTOEXTEND ON ONLINE';

EXECUTE IMMEDIATE query_string;

end;
/

But when I execute the stored procedure,

execute testprc

I am getting the following error:

test..

  BEGIN testprc; END;

  ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYSTEM.TESTPRC", line 10 ORA-06512: at line 1

When I create the tablespace from sqlplus, it is created successfully. I have connected to sqlplus as system user.

Please help me in fixing this and your help will be valuable.

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
  More

First of all dont create object under SYSTEM schema. Second in order to execute commands in PL/SQL you should have privileges granted directly to the user not from role. And SYSTEM user inherits the CREATE TABLESPACE system privilege via DBA role, so that you are able to create tablespace directly not from PL/SQL procedure.

SQL> conn sys as sysdba

SQL> grant create tablespace to system;

Grant succeeded.

SQL> conn system/password
Connected.

SQL> exec testprc

PL/SQL procedure successfully completed.

SQL>


Error while duplicating Oracle database after failover, using 11.2.0.4 I've recently updated oracle from version 11.2.0.1 to 11.2.0.4 and my redundant system is no longer able to recover from failover.

I'm using scripts both on primary and standby node to get back to a state where it is possible to install them as a redundant system again.

It is possible to run the reset script over and over again in all states exept after a failover is performed. The only change is the upgrade to 11.2.0.4. it worked like a charm in 11.2.0.1

I am using Data Guard Broker to maintan the redundancy.

The setup procedure is complicated and is hard to explain here, so I am just asking if someone have faced the same error, and might have a solution how to recover from such situation.

At least, this is the output where it fails: (Backup step is performed prior to duplicate action)

2014-11-14 17:08:55 : Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 14 17:08:48 2014
2014-11-14 17:08:55 : Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
2014-11-14 17:08:55 : connected to target database: REF15 (DBID=276671931)
2014-11-14 17:08:55 : RMAN> CONNECT AUXILIARY *
2014-11-14 17:08:55 : 2> RUN {
2014-11-14 17:08:55 : 3> SET UNTIL sequence = 159 thread = 1;
2014-11-14 17:08:55 : 4> ALLOCATE AUXILIARY CHANNEL CH1 TYPE DISK;
2014-11-14 17:08:55 : 5> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
2014-11-14 17:08:56 : 6> RELEASE CHANNEL CH1;
2014-11-14 17:08:56 : 7> }
2014-11-14 17:08:56 : 8> exit;
2014-11-14 17:08:56 : connected to auxiliary database: REF15 (not mounted)
2014-11-14 17:08:56 : executing command: SET until clause
2014-11-14 17:08:56 : using target database control file instead of recovery catalog
2014-11-14 17:08:56 : allocated channel: CH1
2014-11-14 17:08:56 : channel CH1: SID=25 device type=DISK
2014-11-14 17:08:56 : Starting Duplicate Db at 14-NOV-14
2014-11-14 17:08:56 : released channel: CH1
2014-11-14 17:08:56 : RMAN-00571: ===========================================================
2014-11-14 17:08:56 : RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
2014-11-14 17:08:56 : RMAN-00571: ===========================================================
2014-11-14 17:08:56 : RMAN-03002: failure of Duplicate Db command at 11/14/2014 17:08:51
2014-11-14 17:08:56 : RMAN-05501: aborting duplication of target database
2014-11-14 17:08:56 : RMAN-20206: log sequence not found in the repository
2014-11-14 17:08:56 : Recovery Manager complete.

Update:
Log sequence number (in this case: 159) is fetched from the following query:

    select max(sequence#) from v$archived_log;

Thanks (originally posted in std Stack Exchange)

The message indicates that no archivelog exists with this sequence in the current incarnation.

Failover starts a new incarnation, and log sequences start from 1 again. Let's say your database is at sequence 10 now, and the log sequence was 159 before the failover. V$ARCHIVED_LOG at this point still contains entries about previous incarnation, so the above query will return 159 because it doesn't consider the start of a new incarnation (resetlogs).

For these kind of queries always check resetlogs_change# or resetlogs_time, e.g:

select max(sequence#) from v$archived_log join v$database using (resetlogs_change#);

Fix this and try duplicating again. Or just skip the UNTIL clause and let log shipping take care of the archivelogs.


MySQL InnoDB or MySQL NDB Cluster which one to use We have an application with 8000 users accessing in parallel(max). We are trying to bring in horizontal scaling. I came across MySQL NDB cluster. Should I go with it? Just for auto sharding and other features if I go with it, will it impact performance?

Note:

Most of the tables in our database has foreign key relationship.
It is a B2B application.
This is one of the deployments.
The db size is 900 MB.
As customers grow the size is expected to grow.
Both outer as well as inner Joins are used frequently.
Host machine is Windows Server 2012
The present 8000 user count is expected to grow to 100000 within 2 weeks.
Queries per second with 8000 users = 110.086.

If not NDB Cluster, then what is the best way to bring in horizontal scaling? with less pain like auto sharding, auto replication,...etc.

NDB is more complex to set up, but may provide a faster way to get to 13K qps sharded across multiple machines.


how to deal with a SSIS 2005 validation error in a script component Within a script component, a strange error occurs.

the (vb.net) code leads to build succeeded
as mentioned, build done

BUT and that's not really awesome  (and, yes, design script was clicked):

  Error 1   Validation error. Load Observations: Script Load into
  MeasuringParameters & Observations [194]: The script component is
  configured to pre-compile the script, but binary code is not found.
  Please visit the IDE in Script Component Editor by clicking Design
  Script button to cause binary code to be
  generated.    LoadTransMeas.dtsx  0   0

any hints how to resolve this issue?

Since you're dealing with 2005, there were some known issues with scripts.

For the reported version of 9.0.5057, you'd want to look at KB932556

Earlier version (RTM) of SQL Server would have KB932557 applied


Postgres Dump To TXT File Is there a way to convert a Postgres dump file to a text file?

I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text.

Here's what lastest.dump looks like:

This is a dump file produced by pg_dump -Fc.

To create a text file containing SQL commands from it, use pg_restore.

The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).


Copy Database from Production to Development I need to copy database from production to development instance in SQL Server 2012 as I don't want to mess up the production server database.

Can someone suggest how I can do this ?

Backup from PROD with COPY_ONLY, compression and restore it on DEV server is the best option.

Make sure to enable Instant File initialization to cut down the restore time.

Edit:

  I don't have a permissions to restore database. is it safe to use copy database ?

This might imply that you are not authorized to do such operation. You can ask your DBA to help you out once you have put in a proper support ticket.

Also, its quiet important to wipe out PII information when you do a restore on a NON PROD system - as @MichaelGreen pointed out.


Is it possible to find when the last insert took place? I have several tables that I'm not sure whether they are still in use by some old tools. Not all of them have columns with timestamps so I was wondering if there is some secret internal sql-server table where I could see when the last insert (or any other operation) took place for certain tables?

Not giving away any code or anything just yet but ways to detect whether a DB is still in use by some app can be as follows:
- turn the DB off (set to single user mode, set as read-only, etc) many variations to effectively get the result of someone shouting at everybody else because one of their processes stopped unexpectedly and its because of the server you just "turned off"
.. personally this is my favourite approach - but I do understand that it may cause some real business issues and some people just can't deal with emotions ;).

you could also monitor the database size and see if that changes
you could have a small process that counts all rows from particular tables (all tables) and logs that into some other database for you.. then simply see if the row count changes
you could monitor the log file (if anything happens in terms of INSERT, UPDATES, DELETES, then you will see that there.
you could run sp_who(2) at regular intervals to check who's connected

These are just quick and easy to implement methods to see if someone, somewhere is still using the database.

I hope this helps,

B


You need to divide the rows into groups using GROUP BY. Something like:

select group_concat(product_no), date_sold, shift_start
from sales
group by date_sold, shift_start;

I assume you did something like:

select group_concat(product_no), date_sold, shift_start
from sales

The latter is invalid in most (all but MySQL?) DBMS products, but MySQL allows it by default.


What does a "Buffer cache hit ratio" of 9990 mean? I got this query from a blog post:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

The post said that it would give me a percentage of hits to the cache.  It seemed to indicate that it would be a value of 0-100 (it showed a result of 87).

But when I run it I am getting very high numbers.  Here is an example:

object_name               counter_name             cntr_value
SQLServer:Buffer Manager  Buffer cache hit ratio   9990

Does this mean 99.90 %?

If not, what does it mean?  And how can I get the real value?

NOTE: I have gotten values as low as 257 and as high as 352363

Incase it is relevant, here are a few other server stats:

Page life expectancy: 145
Page reads/sec: 1,380,009,009

Confusing, right?

Well, to actually get the ratio, you'll need to do it yourself using the Buffer cache hit ratio base in addition to the Buffer cache hit ratio by taking the result from Buffer cache hit ratio / Buffer cache hit ratio base.

Try the below query (from Less Than Dot), which should give you the % you're looking for:

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value, OBJECT_NAME
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'


Decrease size of database (with an expression index)? This is my current table definition in a Postgres 10.1-1 database:

CREATE TYPE CUSTOMER_TYPE AS ENUM
('enum1', 'enum2', 'enum3', '...', 'enum15');     -- max length of enum names ~15

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,     -- max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                   -- max 35 char String
   ATTRIBUTE_TWO   TEXT,                          -- 1-80 char String
   PRIVATEKEYTYPE  CUSTOMER_TYPE                  -- see enum
);

It results in about 4.3x more database size compared to the size of the inserted data. (50 MB, 700.000 lines --> database size is 210 MB)

Attribute_One is computed as hash(Customer_One).

Requirements: fast searches (using algorithms) for columns CUSTOMER_ONE and ATTRIBUTE_ONE. (That's why I think I need an index.)

Typical search query:

select * from customer
where Customer_One='XXX' OR Attribute_One='XXX';

Each SELECT can find a maximum of 1 or 0 matching rows in millions of rows.

Is it possible to further decrease the DB size? I have been told to use an expression index but don't fully understand how this works. A short explanation with an example index or other solution would be great

Is the insert speed effected by those indexes? The faster the better. (To be clear: search speed is more important than insert speed.)

If hash() is an IMMUTABLE function (which should be the case for a function called "hash"!) you can omit storing the functionally dependent attribute_one in the table altogether and add an expression index to support queries on the expression hash(customer_one):

CREATE TABLE customer (
   privatekeytype customer_type     -- move the enum to 1st pos to save some more
 , customer_one   text PRIMARY KEY
 , attribute_two  text
);

Expression index:

CREATE INDEX customer_attribute_one_idx ON customer (hash(customer_one));

This is exactly as big (identical) as the index supporting your original UNIQUE constraint on the redundant column attribute_one.

Query:

SELECT *
FROM   customer
WHERE  'XXX' IN (customer_one, hash(customer_one));

Testing with EXPLAIN you'll see index or bitmap index scans like:

->  BitmapOr  (cost=5.34..5.34 rows=5 width=0)
     ->  Bitmap Index Scan on customer_pkey  (cost=0.00..2.66 rows=1 width=0)
           Index Cond: ('XXX'::text = customer.customer_one)
     ->  Bitmap Index Scan on customer_attribute_one_idx  (cost=0.00..2.68 rows=4 width=0)
           Index Cond: ('XXX'::text = hash(customer.customer_one))

About the same performance as with the redundant table column or faster since the table is smaller, yet - which helps overall performance in various ways.

Moving the enum to first position saves a few bytes of alignment padding per row as explained in my previous answer:

Why is my database 12 times bigger than inserted data?

Why does the function have to be IMMUTABLE? See:

How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
Does PostgreSQL support “accent insensitive” collations?


reusing space after deleting lots of rows I have a database which is 20 GB and it has history information since year 2001.
Now I started deleting data prior to year 2008. after completion of deletion how can i reuse the space that is cleared by my delete statement.
I am using SQL Server 2005.

Will Sql server automatically reuse the space if I don't shrink the datafiles?
Should we rebuild the indexes?

SQL Server will reuse available space in the datafile as necessary. If there is no space left it will attempt to auto-grow the datafile(s) for that File Group based on the auto-growth setting. If auto-grow is disabled and there is no room left, then you will get an error trying to do something that would require more space.

With regards to deleting large amounts of records and potentially not seeing that space get reclaimed as "available", you probably need to rebuild the Clustered Index on that table. I have run into situations where I had a rather large (many hundreds of millions of rows) table that would get a new 30 million or so new rows per day and delete the oldest 30 million or so records. We set up a weekly job to do a full rebuild on the clustered index and that is the only time the space was reclaimed. I am not sure of the exact reason that the rebuild was required (maybe the background process that removes the deleted rows couldn't do that task due to high activity on the table?).


Statistics disappear after incremental update We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the statistics disappear after the index is rebuilt.

Below is a script to replicate the issue in SQL Server 2014 with the AdventureWorks2014 database.

--Example against AdventureWorks2014 Database

CREATE PARTITION FUNCTION TransactionRangePF1 (DATETIME)
AS RANGE RIGHT FOR VALUES
(
   '20130501', '20130601', '20130701', '20130801',
   '20130901', '20131001', '20131101', '20131201',
   '20140101', '20140201', '20140301'
);
GO

CREATE PARTITION SCHEME TransactionsPS1 AS PARTITION TransactionRangePF1 TO
(
  [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
  [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
  [PRIMARY], [PRIMARY], [PRIMARY]
);
GO

CREATE TABLE dbo.TransactionHistory
(
  TransactionID        INT      NOT NULL, -- not bothering with IDENTITY here
  ProductID            INT      NOT NULL,
  ReferenceOrderID     INT      NOT NULL,
  ReferenceOrderLineID INT      NOT NULL DEFAULT (0),
  TransactionDate      DATETIME NOT NULL DEFAULT (GETDATE()),
  TransactionType      NCHAR(1) NOT NULL,
  Quantity             INT      NOT NULL,
  ActualCost           MONEY    NOT NULL,
  ModifiedDate         DATETIME NOT NULL DEFAULT (GETDATE()),
  CONSTRAINT CK_TransactionType
    CHECK (UPPER(TransactionType) IN (N'W', N'S', N'P'))
)
ON TransactionsPS1 (TransactionDate);

INSERT INTO dbo.TransactionHistory
SELECT * FROM Production.TransactionHistory
--  SELECT * FROM sys.partitions
--  WHERE object_id = OBJECT_ID('dbo.TransactionHistory');

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId)
  WITH (DATA_COMPRESSION = ROW, STATISTICS_INCREMENTAL=ON)
  ON TransactionsPS1 (TransactionDate)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT 'Stats are avialable'

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

PRINT 'After online index rebuild by partition stats are now gone'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Rebuild the stats with a rebuild for all paritions (this works)'
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = ALL WITH (ONLINE = ON , DATA_COMPRESSION = ROW,
  STATISTICS_INCREMENTAL = ON)

PRINT 'Stats are back'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Works correctly for an offline rebuild by partition'
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = 9 WITH (ONLINE = OFF , DATA_COMPRESSION = ROW)

    --stats still there
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT' stats are gone!!!!!!'

As shown, we cannot rebuild indexes by partition online without losing all statistics for index. This is a major maintenance issue for us. It almost appears that stats incremental option needs to be a part of the single index rebuild syntax or the online option needs to properly handle it like the offline option does.

Please let me know if I am missing something?

Updates:

As far as our need for incremental stats: We are partitioning on an internal customer id and not a date. So when a new client is brought in (large back-load of data) we can simply update the stats for the partition and quickly avoid any ugly plans being created for this new customer. I think I will file it with Microsoft as a bug and see what they have to say and go with the solution of just re-sampling the stats for that partition.

Connect bug report:

Statistics disappear after online index rebuild with incremental statistics

Update:
Microsoft has confirmed that it is a bug.

Not sure if it's a bug, per se but it's definitely an interesting occurrence. Online partition rebuilds are new in SQL Server 2014 so there may be some internals to sort through with this.

Here's my best explanation for you. Incremental statistics absolutely require that all partitions be sampled at the same rate so that when the engine merges the stats pages it can be confident that the sampled distribution is comparable. REBUILD necessarily samples data at a 100% sample rate. There's no guarantee that the 100% sample rate on partition 9 is always going to be the exact sample rate of the rest of the partitions. Because of this, it appears as though the engine cannot merge the samples and you end up with an empty stats blob. However, the statistics object is still there:

select
    check_time = sysdatetime(),
    schema_name = sh.name,
    table_name = t.name,
    stat_name = s.name,
    index_name = i.name,
    stats_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
    s.stats_id,
    s.has_filter,
    s.is_incremental,
    s.auto_created,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    modification_counter
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i
    on s.object_id = i.object_id
    and s.name = i.name
outer apply sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
where t.name = 'TransactionHistory' and sh.name = 'dbo'

You can fill the blob through any number of means:

UPDATE STATISTICS dbo.TransactionHistory (IDX_ProductId) WITH RESAMPLE;

or

UPDATE STATISTICS dbo.TransactionHistory (IDX_ProductId) WITH RESAMPLE ON PARTITIONS (9);

or you can wait for AutoStats to update on the first compilation of a query plan using that object:

-- look at my creative query
select *
from dbo.TransactionHistory
where TransactionDate = '20140101';

Having said all of that, this enlightening post by Erin Stellato highlights what has come to be perceived as a major deficiency of incremental stats. Their partition-level data are not are not used by the optimizer in query plan generation, reducing the presumed benefit of incremental statistics. What, then, is the current benefit of incremental statistics? I'd submit that their primary utility is in an ability to sample large tables more consistently at a higher rate than with traditional statistics.

Using your example, here's how things look:

set statistics time on;

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

--SQL Server Execution Times:
--  CPU time = 94 ms,  elapsed time = 131 ms.

update statistics dbo.TransactionHistory(IDX_ProductId)
with resample on partitions(2);

 --SQL Server Execution Times:
 --  CPU time = 0 ms,  elapsed time = 5 ms.

drop index IDX_ProductId On dbo.TransactionHistory;

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId)
  WITH (DATA_COMPRESSION = ROW)
  ON [PRIMARY]

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

 --SQL Server Execution Times:
 --  CPU time = 76 ms,  elapsed time = 66 ms.

A fullscan statistics update on the incremental statistic costs 131 ms. A fullscan statistics update on the non partition-aligned statistic costs 66 ms. The non-aligned statistic is slower most likely due to overhead incurred with merging the individual statistics pages back into the main histogram. However, using the partition-aligned statistic object, we can update one partition and merge it back into the main histogram blob in 5 ms. So at this point the administrator with the incremental statistic is faced with a decision. They can decrease their overall statistics maintenance time by only updating partitions would traditionally need to be updated, or they can experiment with higher sample rates such that they potentially get more rows sampled in the same period of time as their previous maintenance timeframe. The former allows breathing room in the maintenance window, the latter might push statistics on a very large table to a place where queries get better plans based on more accurate statistics. This is not a guarantee and your mileage may vary.

The reader can see that 66 ms is not a painful statistics update time on this table so I tried setting up a test on the stackexchange data set. There are 6,418,608 posts (excluding StackOverflow posts and all posts from 2012 - a data error on my part) in the recent dump that I downloaded.

I've partitioned the data by [CreationDate] because ... demo.

Here are some timings for some pretty standard scenarios (100% - index rebuild, default - stats auto-update or UPDATE STATISTICS without a specified sample rate:

Create Non-Incremental Statistic with Fullscan: CPU time = 23500 ms,  elapsed time = 22521 ms.
Create Incremental Statistic WIth Fullscan: CPU time = 20406 ms,  elapsed time = 15413 ms.
Update Non-Incremental Statistic with Default Sample Rate: CPU time = 406 ms,  elapsed time = 408 ms.
Update Incremental Statistic with Default Sample Rate: CPU time = 453 ms,  elapsed time = 507 ms.

Let's say we're more sophisticated than these default scenarios and have decided that a 10% sample rate is the minimum rate that should get us the plans we need while keeping maintenance time to a reasonable timeframe.

Update Non-Incremental Statistic with sample 10 percent: CPU time = 2344 ms,  elapsed time = 2441 ms.
Update Incremental Statistic with sample 10 percent: CPU time = 2344 ms,  elapsed time = 2388 ms.

So far there's no clear benefit to having an incremental statistic. However, if we leverage the undocumented sys.dm_db_stats_properties_internal() DMV (below), you can get some insight into which partition(s) you may want to update. Let's say we made changes to data in partition 3 and we want ensure stats are fresh for incoming queries. Here are our options:

Update Non-Incremental at Default (also the default behavior of Auto-Stats Update): 408 ms.
Update Non-Incremental at 10%: 2441 ms.
Update Incremental Statistics, Partition 3 With Resample (10% - our defined sample rate): CPU time = 63 ms,  elapsed time = 63 ms.

Here's where we need to make a decision. Do we take the win of a 63 ms. partition-based update of statistics, or do we bump the sample rate even higher? Let's say we are willing to take the initial hit of sampling at 50% on an incremental statistic:

Update Incremental Statistics at 50%: elapsed time = 16840 ms.
Update Incremental Statistics, Partition 3 with Resample (50% - our new update time): elapsed time = 295 ms.

We're able to sample a lot more data, perhaps setting up the optimizer to make better guesses about our data (even though it's not using partition-level statistics, yet) and we're able to do this more quickly now that we have incremental statistics.

One last fun thing to figure out, though. What about synchronous stats updates? Is the 50% sample rate preserved even when autostats kicks in?

I deleted data from partition 3 and ran a query on CreationDate and checked then checked the rates with the same query below. The 50% sample rate was preserved.

So, long story short: Incremental Statistics can be a useful tool with the right amount of thought and initial setup work. However, you must know the problem that you're trying to solve and then you need to solve for it appropriately. If you're getting bad cardinality estimates, you might be able to get better plans with a strategic sample rate and some invested intervention. However, you're only getting a small portion of the benefit since the histogram being used is the single, merged stats page and not the partition-level information. If you're feeling pain in your maintenance window, then maybe incremental statistics can help you, but it will probably require you setting up a high-touch maintenance intervention process. Regardless, keep in mind the requirements for incremental statistics:

Statistics created with indexes that are not partition-aligned with the base table.
Statistics created on AlwaysOn readable secondary databases.
Statistics created on read-only databases.
Statistics created on filtered indexes.
Statistics created on views.
Statistics created on internal tables.
Statistics created with spatial indexes or XML indexes.

Hope this helps

select
    sysdatetime(),
    schema_name = sh.name,
    table_name = t.name,
    stat_name = s.name,
    index_name = i.name,
    leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
    s.stats_id,
    parition_number = isnull(sp.partition_number,1),
    s.has_filter,
    s.is_incremental,
    s.auto_created,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    modification_counter = coalesce(sp.modification_counter, n1.modification_counter)
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i
    on s.object_id = i.object_id
        and s.name = i.name
cross apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) sp
outer apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) n1
where n1.node_id = 1
    and (
            (is_incremental = 0)
               or
            (is_incremental = 1 and sp.partition_number is not null)
         )
    and t.name = 'Posts'
    and s.name like 'st_posts%'
order by s.stats_id,isnull(sp.partition_number,1)