Do I need to rebuild indexes when upgrading a DB from 2008 to 2012? I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the net that indicate this would be a wise move. Kins great answer here mentions statistics rebuilds but it is for 2000 to 2008

Is this something that was a recommended practice with earlier upgrades or is it something we should still be doing?

I found interesting this link: http://thomaslarock.com/2011/12/7-mistakes-you-cant-afford-to-make-when-upgrading-to-sql-2012/

In my case I upgraded from sql 2000 to 2014 (with a 2008 bridge) detaching and moving files, but considerations are still good. My db whas about 1.3 terabytes.

I run

DBCC CHECKDB WITH DATA_PURITY;

But this is critical just becaming from 2000 or 2005, not your case.
Anyway: if you are moving datafile, you must check db... It is an online operation.
Also:

DBCC UPDATEUSAGE(db_name);

You can skip this.

Reindexes? I didn't find any documents about it. In my case it was even not acceptable. To much time and resources.

I updated statistics. I think this is important when you move a database. This can take long time, yes, but database is available to users. It is mandatory? No, but it makes me feel safe...


Backup audit database We currently have SQL Server 2000 in use. One is LIVE and one is BACKUP. We have an audit database which grows massively. What would be ideal is that at the end of each month, the data gets copied from LIVE to BACKUP and the LIVE data to be flushed (hope this makes sense so far)

I was told you can do this in a number ways but one way I found interesting was to split each month into a different data file and then at the end of the month to move the data file over and create a new one for the next month - any advice on this, or any other solution would be appreciated

Thanks,

JJ

The technique you are refering to is called Table Partitioning. However, that is not available in SQ Server 2000. To the best of my knowledge, it was introduced in SQL Server 2005:

http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx

However, you could build similar functionality by means of scripting:

http://msdn.microsoft.com/en-us/library/aa902650%28v=sql.80%29.aspx


UUID/GUID field and shards I am planning on using UUID field for storing items which can have a parent item. I am using PostgreSQL 9.5. I imagine there will be 3-4 levels at most. The design is to hold a structure such as Corporations->sub-companies->stores/shops So perhaps +/- 2 levels.

Now if I have the item and parent on same table wouldnt it be a problem in future if I want to divide data into multiple databases? What would be the best way to proceed?

                 uuid                 |             parent_uuid
--------------------------------------+--------------------------------------
 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b |
 c569ae30-87af-6bcf-fdd1-8143e3bae27a | 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b
 d7460788-ff07-e82d-83ed-5a0182ae2fbf | 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b

There is no elegant way to proceed. If the hierarchy has an unknown amount of levels, you can't serialize it into fixed relations. But partitioning it is probably a bad idea. Perhaps you want a Graph Database?


DB2 DBM paramter NUMDB has value 8. Should I change it to 1 because I have only one database? Hello I am using IBM Maximo application which is using DB2 database and I have created only one database. I heard that when paramtere NUMDB has value 8 it will divide all values in DB CONFIGURATION PARAMATERS because it will also get memory for other databases which does not exists. Is this true and should I change value from 8 to 1 because I have only one database? All I could find about this is on this link:

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.config.doc%2Fdoc%2Fr0000278.html

Information Center is pretty reliable, and I don't see any indication that it will divide memory for non-existent databases. Do you have another link that indicates that?

Otherwise, it is meant to be the maximum number of allowable activated/connected to databases. So there is no need to set it high (I believe by default it is set to something like 12).

Based on the page you have, it is recommended that you set it to the number of databases you have + 10% for room for growth. So if you have 1 database, you would want 1 database + 10% (which in this case would round up) so you would set it to 2.

The reason I believe IBM recommends this, is that you can sometimes get caught with NUMDB less than your number of databases. We have had this happen to us on several occasions in our development and QAT environments. We set the number of databases to something like 9 or 10. And then the developers decided they needed another database. We DBA's ran the CREATE DATABASE script like usual and it didn't fail. However, when the developers tried to access the new database, they got errors (I don't remember the exact SQLCODE off the top of my head). And it turned out we needed to up the value of NUMDB as we had reached the maximum number of activated/connected databases.

So I would follow IBM's recommendation, which in your case would be to set NUMDB to 2. This gives you what you need with some room for growth.

I think this also might not be a "hot" setting, thus requiring you to run db2stop and db2start for it to take affect.


MySQL Trigger problem with the email address (cause contains the @ sign) I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger:

CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO prova (provaemail)
    VALUES (NEW.email);
END

I obtain an empty field in the 'prova' table.

If I try to "copy" another field it's all ok, the problem seems to be only with the email address (cause contains the @ sign).

I ran an experiment. I created the following sample data and used your stored procedure:

drop database vito;
create database vito;
use vito
create table users
(id int not null auto_increment,
email varchar(255) not null,
primary key (id));
create table prova
(id int not null auto_increment,
provaemail varchar(255) not null,
primary key (id));
DELIMITER $$
CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO prova (provaemail)
    VALUES (NEW.email);
END $$
DELIMITER ;
insert into users (email) values
('redwards@logicworks.net'),
('rolandoedwards@yahoo.com');
SELECT * FROM users;
SELECT * FROM prova;

Here is the result:

mysql> drop database vito;
Query OK, 2 rows affected (0.07 sec)

mysql> create database vito;
Query OK, 1 row affected (0.00 sec)

mysql> use vito
Database changed
mysql> create table users
    -> (id int not null auto_increment,
    -> email varchar(255) not null,
    -> primary key (id));
Query OK, 0 rows affected (0.11 sec)

mysql> create table prova
    -> (id int not null auto_increment,
    -> provaemail varchar(255) not null,
    -> primary key (id));
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
    -> BEGIN
    ->     INSERT INTO prova (provaemail)
    ->     VALUES (NEW.email);
    -> END $$
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;
mysql> insert into users (email) values
    -> ('redwards@logicworks.net'),
    -> ('rolandoedwards@yahoo.com');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM users;
+----+--------------------------+
| id | email                    |
+----+--------------------------+
|  1 | redwards@logicworks.net  |
|  2 | rolandoedwards@yahoo.com |
+----+--------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM prova;
+----+--------------------------+
| id | provaemail               |
+----+--------------------------+
|  1 | redwards@logicworks.net  |
|  2 | rolandoedwards@yahoo.com |
+----+--------------------------+
2 rows in set (0.00 sec)

mysql>

Looks like the trigger works fine to me.

Rhetorical questions:

Is the version of MySQL you are using source compiled or RPM/YUM installed ???
Should you upgrade to the latest version of MySQL ???


single select vs multiple selects using limit and offset Working on a fairly high volume site. There are some tables example - the customers table which gets hit a lot.

The problem

We are writing a piece of code that selects a bunch of customers and sends out mailers etc (nothing mission critical). These selects often result in about 20000 records. We obviously do not want the regular hits to the customer table being blocked by these select statements.

We went down the route of making these reads readUncommitted since the operations on the data are absolutely not mission critical.

However, instead of making these massive selects if in code we make a select with a limit and an offset and keep looping would that cause less locks on the db.

In other words - would 20 select statements each retrieving 1000 records be more desirable than 1 select statement retrieving 20000 records in terms of reducing locking etc.

Any suggestions would be greatly appreciated.

Okay, first thing, I need to pick at this a little bit:

  We went down the route of making these reads readUncommitted since the
  operations on the data are absolutely not mission critical.

Using READ UNCOMMITTED/NOLOCK should only be considered when the accuracy of the results is not critical, because that's what the transaction isolation level affects. I understand that the operation is not mission-critical, but that should not be the driver behind selecting a less restrictive isolation level.

  In other words - would 20 select statements each retrieving 1000
  records be more desirable than 1 select statement retrieving 20000
  records in terms of reducing locking etc.

If you continue to use READ UNCOMMITTED, the locking overhead will be the same between the two methods (i.e., no locks will be taken). However, running multiple statements instead of one can affect other things, such as query compilation (which uses CPU), network chattiness, and the complexity of your application. In this case, I think the operation of sending a mailer would be relatively infrequent (I could be wrong), so the latter factor may be the most important to consider.

If you choose to switch back to a higher isolation level for accuracy reasons, that is an entirely different ball game in terms of locking.


How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling? Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling.

What is the best pattern to use for this kind of set up?  Should I have one-engine-per-process, using a ConnectionPool, or should I create an engine per-request, and use NullPool for each one of them?  Is there a different pattern altogether that I should be using?

Thanks very much!  Let me know if more information is needed and I'll update ASAP.

with PGBouncer, you'd probably want to just stick with NullPool.  In that case you may be able to share a single Engine across subprocesses since no socket connections will be carried over the subprocess boundary.  But you can't share anything referring to a Connection object, like a Session with an active transaction, over this boundary.    You definitely wouldn't want to do "engine-per-request" though, an Engine is an expensive object that accumulates a lot of information about a particular database URL the first time it sees it.


Migrating ssrs reports between different domains I have an SSRS server in Domain A and i was asked to copy all the folders and reports from domain A to an SSRS server in domain B.

The network team has opened the ports between the two servers in the different domain and i can connect to the web browser of each SSRS from the opposite server.

When i copy reports between server in the same domain i usually user rs service commands. i tried doing the same thing between the two domains and recieved this error:

  could not connect to server http://localhost/[sourceserver]/reportservice2010.asmx

for reference:
server A - is the source server in domain A
server B - is the destination in domain B

this is the command i'm running on CMD:

     rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://[server A IP]/reportserver -u Domain A\username -p domain A password -v f="/foldername" -v ts="http://[server B ip]/Reportserver" -v tu="Domain B\username" -v tp="Domain B password" -v tf="/foldername" -v security="True"

also important to mention the the destination server already has reports on it so i cant restore the reportserver DB to it.

what can i do to solve this?
Thanks

We have had some good experiences using ReportSync to copy selected folders from one report server to another.

https://gallery.technet.microsoft.com/SSRS-How-to-Import-Export-28660d6e

The tool is free and straighforward to use.


How to script database validations? I want to create a smart way to execute a SQL script when rolling out DB changes into QA, UAT and production environments in order to smoke test database changes are correct after maintenance has been completed.

What's the best way to do this?

For now I am running multiple such SQL selects to do a visual compare of existing DB values to expected values. Here is my code:

select value as DBvalue, 'text' as PRODvalue -- could be 'text' or 'html'
from ConfigParams
where paramname = 'EmailBodyType'
and environment = 'prod'

Then the result will be a two-field result set that shows DB values next to expected values.

I have a listing of 10 or so SQL selects to validate param values are updated and correct after the DB work is done. Is there a better way?

You can create another table with expected records value and each time compare both tables records.
for compare data between two table you can use SQL Data Compare of Redgate package.


How to ensure a field is populated before use in a trigger When I assign a new bed number in a hospital, the number must be prefixed by the ward number the bed is in. How do I know my user is going to populate Ward_Id before Bed_Id? What does one do in a case like this?

There is only one table:

CREATE TABLE [dbo].[Bed](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Bed_Id] [nvarchar](10) NOT NULL,
    [WardId] [int] NOT NULL,
    [Private] [bit] NOT NULL,
    [SpecialInfo] [nvarchar](max) NULL,
    [AdministratorId] [int] NOT NULL,
 CONSTRAINT [PK_Bed] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

When a bed gets added, Bed_Id must be prefixed by WardId.

Community wiki answer:

You could use a computed column for BedId. No need to be actually stored.

Would something like Bedid AS CONCAT(WardId, '-', Id) work for you?


Deleting aged rows from postgresql table which don't have related entries in another table I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied.

The postgresql database is like so: there's a table called links_link, and another one called links_publicreply. links_publicreply has a column called answer_to, which is a foreign key to links_link. I only want to delete aged rows in links_link which do NOT have a related entry in links_publicreply.

What would the queries be? Something like the following?

begin;
DELETE FROM links_link WHERE id NOT IN links_publicreply AND "submitted_on" < now() - interval('1 day');
commit;

I don't want to run into foreign key violations.

Use a subrequest : this should work

DELETE FROM links_link WHERE id NOT IN (select answer_to from links_publicreply ) AND `submitted_on` < CURRENT_DATE-1 ;

You can have of course multiple subrequest chained.


Need to find duplicate entries I received a database with a few million records in it, but apperently there might be duplicate records in them.

A user enters data into the database and a primary key is generated, however if the user enters the same data again, a new primary key will be generated for that data, even though the data has already been entered before. There are no checks on this.

I need to go looking for these duplicates, but I do not really know where to start. I first thought concatenating all cells except the primary key in a subquery and then count these rows and see which ones have a count higher than 1.

cfr.

pkey    recipe     fkey    comment
1   toast       3       tasty
2   curry       2       spicy
3   curry       2       spicy
4   bread       1           crumbly
5   orios       2       cookies

Here the curry entries are identical and I'd have to delete 1 of those.

However I read concatenating is unpredictable in mysql and it just feels a bit wrong to me as well.

Any hints ?

Suppose your table is called ingredients. Try the following:

Step 01) Create an empty delete keys table called ingredients_delete_keys

CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;

Step 02) Create PRIMARY KEY on ingredients_delete_keys

ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);

Step 03) Index the ingredients table with fk,recipe,pkey

ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);

Step 04) Populate the ingredients_delete_keys table

INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;

Step 05) Perform a DELETE JOIN on ingredients table using keys that don't match

DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;

Step 06) Drop the delete keys

DROP TABLE ingredients_delete_keys;

Step 07) Get rid of the fk_recipe_pkey_ndx index

ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;

OK Here are all the lines in one block...

CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;
ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);
ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);
INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;
DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;
DROP TABLE ingredients_delete_keys;
ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;

Give it a Try !!!

CAVEAT

Notice that using MIN function helps keep the first pkey entered for fk. If you switch it to MAX function instead, the last pkey entered for fk is kept.


Extract All Links table I have a table just like attached image.

The query i am using to fetch the data is

SELECT  message_text
FROM    messages
WHERE   conversation_id = "69_P"
AND     message_type = "text"
AND     message_text LIKE "%http%"
LIMIT 3

With this query i get the data with links as well as text. So is there any way to grep only links that start with http or www and ends with .domain or any?

Result Output

https://www.1and1.com/
Test  https://www.google.com
Test message with link  https://momentjs.com/docs/

Desired output

https://www.1and1.com/
https://www.google.com
https://momentjs.com/docs/

LIKE '%http%www%domain.com%'

will find the lines.  But SQL cannot extract the desired part.  Instead, do the extraction in your application code.

OR...

Redesign your schema to already have the URL in a separate column.


Linked server connections to Multi-subnet failover cluster We have recently migrated our SQL Server distribution database to a multi subnet failover cluster, and have found that occasionally when adding articles/viewing replication status from the publisher we get errors like:

  Cannot initialize the data source object of OLE DB provider
  "SQLNCLI11" for linked server "repl_distributor". (.Net SqlClient Data
  Provider)

This appears to be due to the linked server connection talking to a multi subnet failover cluster (as described here)

Other than following the solution of implementing an ODBC connection on each node of our publisher (also a multi subnet failover cluster) is there anything else we can try to fix linked servers connecting to a multi subnet FCI?

The solution you linked to overcomes the problem with connecting to multi-subnet failover groups by using the MultiSubnetFailover=True connection string setting.

The way that setting deals with the problem is attempting many connections in parallel, rather than one after the other (the default behavior).  This increases the chances that a connection will be made successfully before reaching the connection timeout limit.

If you don't want to use that option (you mentioned not wanting the overhead of configuring ODBC connections on all your cluster servers), another solution would be to increase the (linked) server-level connection timeout setting to a value that's high enough to deal with the serial connection attempts.

The default connection timeout on linked servers is 10 seconds (represented by "0" on the settings screen).  You can increase the timeout to 20 seconds by doing the following:

EXEC master.dbo.sp_serveroption
    @server=N'YourLinkedServerName',
    @optname=N'connect timeout',
    @optvalue=N'20'

If you need to change the connect timeout to something higher because seconds 20 seconds is too low:

EXEC master.dbo.sp_serveroption
    @server=N'repl_distributor',
    @optname=N'connect timeout',
    @optvalue=N'60'

The docs on the default value for "connect timeout" are a little confusing.

sp_serveroption says

  Time-out valuein seconds for connecting to a linked server.

  If 0, use the sp_configure default.

sp_configure doesn't support an option called "connect timeout" but has one called "remote login timeout" (see Server Configuration Options).  The description from here sounds like the thing we're looking for:

  The remote login timeout option specifies the number of seconds to wait before returning from a failed attempt to log in to a remote server.
  ...
  The default value for this option is 10 seconds.

In practice, I just created a linked server locally with the default settings, turned off the target instance, and tried to query it.  Timed out at 10 seconds =)


Reorganizing a subject area in ERwin I have a data model in ERwin (r7) that has about 1000 tables in it. I'm right now in the process of splitting it up into various different subject areas to make it a bit more manageable.

Here's the issue: I create a subject area with approx. 50 tables. The problem is, they appear all over the subject area in seemingly random places. Even if I zoom out as much as possible, I can't see the entire subject area, and I need to scroll around to reach the tables. Additionally, there's an obscenely large amount of unnecessary blank space between the tables.

Is there some sort of shortcut to condense all the tables in the subject area into one area (especially when there are tables that are also connected through relations)? Currently I'm moving every piece one or two at a time, but I'm hoping there's a quicker way to organize everything.

Thanks in advance for your help!

Erwin has an autolayout function that may help.

Another way you can regorganize things is to right click on a relationship line, select go to parent or go to child (whichever is appropriate) and then just drag the entity to where ever you want it.

You can also use the fit to model zoom function.

Hope this helps


Does Clustered Index on GUID create more fragmentation than Non Clustered Index? I have a simple table:

CREATE TABLE [dbo].[UserTestGroups](
    [UserTestGroupId] [int] IDENTITY(1,1) NOT NULL,
    [Token] [nvarchar](100) NOT NULL,
    [TestId] [bigint] NOT NULL,
    [Group] [tinyint] NOT NULL,
    [InsertDate] [datetime] NOT NULL)

The table will have a relatively large number of inserts - initially 10,000 sessions with 8 rows per session for a total of 80,000 daily inserts. We expect that to increase significantly in the near future. Regardless, we try to make our platform as resilient as possible, and not just to be OK with current load.

The table will be used for reporting, which can be considered a secondary requirement.

Since we are using Entity Framework (Microsoft's ORM) all tables that are written to need a logical primary key, so I added an Identity column that I simply ignore. I don't like having composite keys, they tend to make nightmare with ORMs, so unless I absolutely have to, I add another identity column and make it the PK.

All the BI report's queries will be based on the Token, which is a sessionId - the GUID, so I created the clustered index on that token column.

Our DBA, who is a SQL Server MVP, told me that having the clustered index on a GUID column will cause fragmentation and that I should create the clustered index on the IDENTITY column, and create a nonclustered index for the Token column.

I don't understand, won't the nonclustered index have the same fragmentation problem? Why would duplicating the data into a new index be better than using a clustered index?

The token-Guid isn't the PK, the PK is a composition of all the columns together (including the InsertDate).

Currently (for I really don't know what reason) all GUID values are stored as NVARCHAR(100), I don't know what's the historical reason-justification for that, but that is what we have.

 Our DBA is overseas and our communication is a tweet long, so I can't get a proper answer until he comes back

Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending number, which is guaranteed to (almost) never cause page-splits on the table.

Also, don't store the GUID in an nvarchar(100) column, use the data type designed for them, which is uniqueidentifier.

Look carefully at the reporting requirements prior to adding indexes to this table.  You may find out you don't need an index on the GUID column at all.  If you determine that having an index on the GUID column is required, you may find it desirable to have the leading column of the index not be the GUID, perhaps it will be the Group or TestID column instead?

If you've determined, through a rigorous design process, that the majority of reporting queries against this table will be made using the session id as the primary component of joins or where clauses, you may actually benefit from the table being clustered on the session id.  You'd likely want to set the page fill factor at some point below 100%, perhaps start at 93% and monitor fragmentation levels.  Also, it's worth pointing out that fragmentation may not be such a massive problem if you store this table on SSD.  Will the session id be propagated throughout the other related tables?  If so, you'll likely need non-clustered indexes on the column in those other tables as well, compounding the fragmentation issue.  It may be worthwhile maintaining a single table where you insert the GUIDs to obtain a surrogate integer key to use in the other tables, something like:

IF OBJECT_ID('dbo.SessionSurrogate') IS NOT NULL
DROP TABLE dbo.SessionSurrogate;
CREATE TABLE dbo.SessionSurrogate
(
    SessionID int NOT NULL
        CONSTRAINT PK_SessionSurrogate
        PRIMARY KEY NONCLUSTERED
        IDENTITY(1,1)
    , SessionGUID uniqueidentifier NOT NULL
);
CREATE UNIQUE INDEX CI_SessionSurrogate --could be non-unique if required
ON dbo.SessionSurrogate(SessionGUID);

IF OBJECT_ID('dbo.UserTestGroups') IS NOT NULL
DROP TABLE dbo.UserTestGroups;
CREATE TABLE dbo.UserTestGroups
(
    UserTestGroupsID int NOT NULL
        CONSTRAINT PK_UserTestGroups
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SessionID int NOT NULL
    , TestID bigint NOT NULL
    , [Group] tinyint NOT NULL
    , InsertDate datetime NOT NULL
);

GO
CREATE PROCEDURE dbo.InsertSession
(
    @SessionGUID uniqueidentifier
    , @TestID bigint
    , @Group tinyint
)
AS
BEGIN
    INSERT INTO dbo.SessionSurrogate (SessionGUID)
    OUTPUT INSERTED.SessionID, @TestID, @Group, GETDATE()
    INTO dbo.UserTestGroups (SessionID, TestID, [Group], InsertDate)
    OUTPUT INSERTED.SessionID
    VALUES (@SessionGUID);
END;
GO

You'd then insert data like:

DECLARE @SessGUID uniqueidentifier = NEWID();
DECLARE @TestID bigint = 42;
DECLARE @Group tinyint = 6;
EXEC dbo.InsertSession @SessGUID, @TestID, @Group;

The benefit here is you're only causing fragmentation on a single table with a very narrow row width (20 bytes), offering somewhere around 400 rows per page.  The stored procedure above is very compact and outputs the generated int for you to use later, if necessary.


Why does MariaDB choose different index use for the same data? I have this table (this is the output from show create table on both servers, ie. using diff to compare the output shows only the auto increment value, because one has moved on slightly from the snapshot).

Create Table: CREATE TABLE `logs` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip` varchar(39) CHARACTER SET latin1 NOT NULL,
  `method` varchar(6) CHARACTER SET latin1 NOT NULL,
  `endpoint` varchar(1000) CHARACTER SET latin1 NOT NULL,
  `status` int(3) NOT NULL,
  `params` text,
  `data` mediumtext,
  `client_username` varchar(526) DEFAULT NULL,
  `client_ip` varchar(39) CHARACTER SET latin1 DEFAULT NULL,
  `extra_params` text,
  `error_code` int(10) DEFAULT NULL,
  `request_id` varchar(255) DEFAULT NULL,
  `domain_id` bigint(11) unsigned DEFAULT NULL,
  `admin_id` bigint(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_id` (`request_id`),
  KEY `endpoint` (`endpoint`),
  KEY `method` (`method`),
  KEY `client_username` (`client_username`(255)),
  KEY `domain_id` (`domain_id`),
  KEY `admin_id` (`admin_id`)
) ENGINE=MyISAM AUTO_INCREMENT=685835441 DEFAULT CHARSET=utf8

I have exactly the same data on two different servers (both have the same my.cnf), but I get different behaviour. On one, I get a table scan, no matter what, and on the other I get the expected index use (not just with the domain_id index as in the example below, but with any index).

On the good one:

MariaDB [temp_db]> explain select * from logs where domain_id=298506;
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+
| id   | select_type | table           | type | possible_keys | key       | key_len | ref   | rows   | Extra |
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+
|    1 | SIMPLE      | logs            | ref  | domain_id     | domain_id | 9       | const | 119987 |       |
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+

On the bad one:

MariaDB [temp_db]> explain select * from logs where domain_id=298506;
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
|    1 | SIMPLE      | logs            | ALL  | NULL          | NULL | NULL    | NULL | 166719856 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+

They are both using MariaDB 10.1.29, on Debian. I've done an optimise table and analyse table on both.

Asking MariaDB to force use of the index also doesn't:

MariaDB [temp_db]> explain select * from logs force index (domain_id) where domain_id=298506;
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
|    1 | SIMPLE      | logs            | ALL  | NULL          | NULL | NULL    | NULL | 167509512 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

Is there either some explanation for what causes this behaviour, or some way that I can investigate further?

This should temporarily fix the statistics that appear to be screwed up:  Do ANALYZE TABLE logs;; it will take a long time (for MyISAM, but not for InnoDB).
Run CHECK TABLE logs; -- the index may be corrupted.  (This is a MyISAM-only thing to do.)
Use InnoDB, not MyISAM.  InnoDB does not need ANALYZE TABLE nearly as often, and the ANALYZE runs much faster.


Is it possible to name an ACL? I need to create some ACL's to allow TCP connections to some hosts. These hosts can only be accessed by their IP address. To make them easier to keep track of in the future, I'd like to be able to give the ACL some kind of friendly name, but I can't see a way of doing it.

I'm on Oracle 12.2, and from reading the DBMS_NETWORK_ACL_ADMIN documentation it sounds like I'm supposed to use the dbms_network_acl_admin.append_host_ace procedure. I've created the ACL with (details changed to protect the innocent):

BEGIN
    dbms_network_acl_admin.append_host_ace(
        host => '10.10.10.10',
        lower_port => NULL,
        upper_port => NULL,
        ace => xs$ace_type(
            privilege_list => xs$name_list('connect'),
            principal_name => 'USER1',
            principal_type => xs_acl.ptype_db
        )
    );
END;

This appears to have created it OK, but when I look at DBA_NETWORK_ACL_PRIVILEGES, I'm hoping to find some way of making perhaps the ACL field more friendly (I can understand it having an arbitrary ACLID, but it'd be nice to able to one field customisable!)

SQL> SELECT * FROM dba_network_acls WHERE host = '10.10.10.10';

HOST        LOWER_PORT UPPER_PORT ACL                                          ACLID            ACL_OWNER
----------- ---------- ---------- -------------------------------------------- ---------------- ---------
10.10.10.10                       NETWORK_ACL_780DE78AA2A6289CE0531240D80A83F6 0000000080002774 SYS

This may be a bit unorthodox, but you could have a table that allows you to store "friendly names", and comments etc, which you can update/query whenever required. Eg

create table myfriendlynames (
  id number generated always as identity primary key
, aclid varchar2( 256 ) unique
, friendlyname varchar2( 256 ) unique
--, comments varchar2( 256 )
) ;

-- pick up all aclids from dba_network_acls, and store them in the new table
merge into myfriendlynames M
using (
  select aclid from dba_network_acls
) ACLS on ( M.aclid = ACLS.aclid )
when not matched then
  insert ( M.aclid ) values ( ACLS.aclid )
;

Update, set a friendly name ...

SQL> select * from myfriendlynames ;
ID  ACLID             FRIENDLYNAME
1   0000000080002710  NULL

update myfriendlynames
set friendlyname = 'Fred'
where friendlyname is null ;  -- you could also use id here

-- aclid - friendlyname mapping
select ACLS.aclid, M.friendlyname
from dba_network_acls ACLS
  join myfriendlynames M on ACLS.aclid = M.aclid
;

ACLID             FRIENDLYNAME
0000000080002710  Fred

Query / view

-- add/remove columns here as required
select
  ACLS.host
, ACLS.lower_port
, ACLS.upper_port
, substr( ACLS.acl, 1, 16 ) || '...' || substr( ACLS.acl, -4, 4 ) aclshort
, ACLS.aclid
, M.friendlyname
from dba_network_acls ACLS
  left join myfriendlynames M on ACLS.aclid = M.aclid
;

HOST  LOWER_PORT  UPPER_PORT  ACLSHORT                 ACLID             FRIENDLYNAME
*     NULL        NULL        NETWORK_ACL_FD9A...FB6F  0000000080002710  Fred


Is it possible to attach Oracle database files after a clean install I had an Oracle 10g instance on my Win2008R2 server. Unfortunately, the server crashed and we had to re-install the operating system on the C: drive. The oracle files are on drives other than C.
The other drives (except C) are restored from disk backups. I don't have a dump file, I only have the Oracle data files such as USERS01.DBF and the Oracle control files.
Now I am trying to find a way to install Oracle again on the server but I am just wondering about how I can attach the current .DBF files to the new Oracle instance? Is this possible? If so, how can I attach the current .DBF files to Oracle?
Currently there is no "OracleService" on services.msc and no listener as well. But previos the previous Oracle instance was installed on the F drive so I still have the Oracle binaries and data files on the F drive.
I would really appreciate if you could provide any information about this.

ORADIM is what you need: Creating an Oracle service on Windows


Extract string in PostgreSQL 9.2 I got the following column:

full_path "gorfs"."absolute_pathname"

It keeps the path of a BLOB, like on the example below:

Note that the 4865 is the account_id.The path is always like: /userfiles/account/ACCOUNTID

I need to extract that, to use in another query. For example:

select * from gorfs.seg WHERE account_id = 4865

How can I extract that value from the full_path column?

If the account id is always at the third position use split_part()

select *
from gorfs.seg
WHERE split_part(full_path, '/', 4) = '4865'

Note that due to the leading / the actual index is 4, not 3.

The following query:

with seg (id, full_path) as (
  values
      (1, '/userfiles/account/4556/attachment/1234'),
      (2, '/userfiles/account/123/attachment/1234'),
      (3, '/userfiles/account/7890/attachment/1234'),
      (4, '/userfiles/account/4556/attachment/56789')
)
select *
from seg
where split_part(full_path, '/', 4) = '4556'

returns

id | full_path
---+-----------------------------------------
 1 | /userfiles/account/4556/attachment/1234
 4 | /userfiles/account/4556/attachment/56789

If you want to put that into a view, you can use:

create or replace view v_seg
as
select st_ino,
       segment_index,
       st_ino_target,
       full_path,
       split_part(full_path, '/', 4) as account_id
from gorfs.seg;


How can I enable/disable MySQL slow query logging on Amazon RDS without restarting I would like to enable/disable slow query logging on an Amazon RDS instance without having to restart. I'm aware that I can do this by changing the slow_query_log parameter in the instance's parameter group, but this requires me to restart.

On the other hand, I've seen that this is possible with SET GLOBAL slow_query_log = 'ON'; in MySQL, but this requires super privileges, which are restricted to the rdsadmin user on Amazon RDS.

I've also found this resource: How do I enable functions, procedures, and triggers for my Amazon RDS MySQL instance? but the description says "when binary logging is enabled", which is not what I need.

The slow_query_log variable is a dynamic variable.

Dynamic variables can be updated without a restart:

  When you change a dynamic parameter and save the DB parameter group,
  the change is applied immediately regardless of the Apply Immediately
  setting.

So just change it and you should see the effects immediately.


importing-a-sql-db-dump-postgres11 pgadmin4 I have a SQL dump file: C:\Users\Rayyan\Desktop\dump_2.sql.

I have been trying to import this for so long. I am using pgadmin 4, and since I could find nothing in that GUI to help, I tried using Windows command prompt.

I created my new database "wecudos" in pgadmin4.

Then tried the following command in windows command prompt:

  psql wecudos < C:\Users\Rayyan\Desktop\dump_2.sql

according to the specified format:

  psql dbname < dumpfile

This is not working, please help, I have also added a screenshot of the error/data I entered, thanks!

try this

C:\Program Files\PostgreSQL\9.6\bin>psql -U postgres dbname < D:\testdump.sql

or

C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres dbname < D:\testdump.sql


CheckDB on Partitioned Table - Insufficient Memory in resource Pool default We are experiencing an issue on one of our servers where CheckDB is blowing the resource pool 'default' ... let me elaborate..

Windows 2012 R2 Standard - 16GB RAM
SQL Server 2012 Enterprise [11.0.5532.0] MAX memory 14GB RAM, MIN memory 0GB
This is a dedicated SQL Server running 1 default instance of Microsoft SQL Server. Vanilla install with no special requirements or configuration.

We can CheckDB all the other databases (max size of 3GB per database, 9 user databases) however when we CheckDB the 5GB database it fails after 10 minutes reporting

  "There is insufficient system memory in resource pool 'default' to run
  this query. [SQLSTATE 42000] (Error 50000)  DBCC execution completed.
  If DBCC printed error messages, contact your system administrator."

This is a vendor database and cannot be altered .. it consists of 2 filegroups, both filegroups contain 248 tables that each contain 1,000 partitions.

I have tried DBCC CheckDB with combinations of ALL_ERRORMSGS NO_INFOMSGS in case that made a difference to memory requirements but there was no change in behaviour. We cannot use TABLOCK as we will not be able to lock tables. I have tried DBCC CheckFileGroup and this returns the same error message.
We can try running PhysicalOnly for a lightweight check but we want to do a full CheckDB once a week.

The total disk space used (not allocated) to all the databases is 8,797 MB .. the RAM allocation to SQL Server and available is 14GB [14,336 MB]

My question is .... the entire 5GB database should fit into memory (and I have checked that more than 5GB of RAM is available..... would this volume of partitions and partitioned tables in a database using 1 or 2 filegroups cause excessive memory consumption? I cannot find any Microsoft documentation that says partitioned tables require more memory...only that partitioned tables over many filegroups can help reduce CheckDB time/resource requirements for VLDB.

I am running DBCC CheckDB WITH ESTIMATEONLY now and will update once I have the results.
UPDATE: ESTIMATEONLY returns the following messages:

  DBCC results for ''. CHECKDB found 0 allocation errors
  and 0 consistency errors in database ''. Msg 701, Level
  17, State 123, Line 1 There is insufficient system memory in resource
  pool 'default' to run this query.

Any input will be greatly appreciated!

Paul's explanation might answer your question , you can take a look here
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-why-would-checkdb-run-out-of-space/. I have copy pasted a snippet from his post related to your question:

There’s another reason that DBCC CHECKDB may stop with an out-of-space error.  DBCC CHECKDB has to validate the contents of the whole database. As such it needs to store info about things it has seen at one point during database scanning so that it can match that info against things it sees at later points during database scanning. These bits of info are stored in an in-memory worktable. In many cases though, the amount of info being stored exceeds the available memory and the worktable needs to spill over to tempdb. (For example, running DBCC CHECKDB against a 1TB database on a machine with 16GB of memory – the amount of info that DBCC CHECKDB needs to store the intermediate pieces of info will likely exceed the memory available to SQL Server). So – if tempdb isn’t big enough to store the worktable, it has to grow. If it can’t grow, then DBCC CHECKDB will fail because it needs the worktable to operate.


What is the proper type for columns holding GeoJSON? What data type do you prefer for columns holding GeoJSON information, in the major RDBMS families (i.e. Oracle DB, PostgreSQL, MySQL, SQL Server, SQLite, DB2..?

In general, do you prefer / is better using large object columns (e.g. CLOB of Oracle DB) or do you prefer / is better using data types from the character / text family (e.g. text in PostgreSQL or character varying (with unspecified length) in PostgreSQL too)?

Can you be so clever, experienced or knowledgeable even to assert whether the answer depends on the max GeoJSON character length in the table, or not? or maybe the median value or something else?

Spoiler for X/Y problem fans follows!

   For the X/Y problem fans, my X question to this Y question would be:
 Which is the proper way of migrating data held in Oracle spatial in production to PostGIS?, but I guess the answer to my X question is kind of a time evolving one, while I guess the answer to my Y question is more of a constant (even almost axiomatic) one.

I would have created the geojson tag if I already could, but I can not yet so I tag as JSON.

What is the proper type for columns holding GeoJSON?

In PostgreSQL, as JSONB (ideally), or JSON.

  Which is the proper way of migrating data held in Oracle spatial in production to PostGIS?

As "Well-Known Binary", (WKB) using Get_WKB. Or simply using oracle_fdw which solves most of these problems and uses WKB behind the scenes.

  The only supported geometry types are POINT, LINE, POLYGON, MULTIPOINT, MULTILINE and MULTIPOLYGON in two and three dimensions. Empty PostGIS geometries are not supported because they have no equivalent in Oracle Spatial.

Moreover on the subject of GeoJSON, PostGIS provides ST_GeomFromGeoJSON, and ST_AsGeoJSON if you want to use GeoJSON.


Divide data in single column to multiple columns I have source data like

Name Is_Trget
a     Yes
s     No
d     Yes
f     Yes
a     Yes
S     No

from this source data expected output is

Name Is_Yes Is_No
a    2      0
s    0      2
d    1      0
f    1      0

I need count of Yes and No for a specific Name.

Can someone help on this.

SELECT Name,
       SUM(CASE Is_Trget WHEN 'Yes' THEN 1 ELSE 0 END) Is_Yes,
       SUM(CASE Is_Trget WHEN 'No' THEN 1 ELSE 0 END) Is_No
FROM source
GROUP BY Name;

fiddle


sys.syshobtcolumns Help Does anyone have a good link to documentation regarding how the syshobtcolumns and syshobt system tables are populated.  The reason for asking is that we are receiving pagelatch_sh and pageliolatch_ex blocking on tempdb.sys.syshobtcolumns.clust (lead blocker).  I realise that the tables are undocumented.

This is caused by a very poor sharepoint query which does a lot of work in the tempdb.  The query is generated by the sharepoint API's.  I understand that our developers can make alterations to the query to reduce the amount of work which it performs.  Lazy coding means that hundreds of irrelevant columns and rows are being returned.

I'm interested to find out how this system table is being blocked.  The query generates a table variable, inserts a bunch of rows and then performs a god awefull query against the data involving spooling etc.  I would attach this but the query is over 60000 chars.  I know that this is causing the blocking but understanding the syshobtcolumns system table will enable me to determine if it is these sharepoint queries causing issues or if we have a more serious issue with our configuration(s).

I have identified 6 sharepoint queries for the developers to look into reducing the number of rows and columns returned.  All are called throughout the day and all have similar workloads in the tempdb.  The blocking and subsequent slow down of all queries on the sharepoint SQL instance occur when the CheckDB weekly job executes.  We see an increase in disk waits during this period once a week.  We are yet to determine if we have exhibiting a tipping point on the SAN and we are effectively hitting a bottle neck when our maintenance tasks execute at the same time of these I/O intensive queries.

We most certainly need to improve these sharepoint queries to reduce contention, however the issues occur at regular times when the maintenance task (checkdb full) executes.

Our setup is on a DL580 a 16 core server, 4 quads, 32Gb ram, 3par san, tempdb has 8 data files, sql server 2005 SP3 cu8 x64 bit enterprise, windows server 2003 R2 x64 bit enterprise, separate luns for data, logs, tempdb etc.  Performance monitoring through SQL Sentry (lead blocker found here along with intensive queries and disk waits).

DBCC CHECKDB uses tempdb and can stress the I/O subsystem heavily when it runs.  It uses tempdb to store facts during the checks it is running.  Paul explains some of the details of this in his blog post (http://sqlskills.com/BLOGS/PAUL/post/How-does-DBCC-CHECKDB-WITH-ESTIMATEONLY-work.aspx) If you are getting latch contention on the system tables in tempdb, the only thing you can really do is reduce your usage of tempdb.  Adding additional files only reduces latch contention on the PFS, GAM and SGAM pages.

Have you configured the SQL instance following Microsofts recommendations for Sharepoint, which includes setting 'max degree of parallelism' to 1 for the instance? (http://technet.microsoft.com/en-us/library/hh292622.aspx)


Does SKIP LOCKED FOR UPDATE work with views? I am currently using SKIP LOCKED FOR UPDATE to schedule tasks as such:

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM task
  WHERE /* ... bunch of logic ... */
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

I would like to abstract the sub-query used to select outstanding tasks using a view, e.g.

CREATE VIEW outstanding_task AS
SELECT *
FROM task
WHERE /* ... bunch of logic ... */

and then use the view to pick tasks for scheduling, e.g.

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM outstanding_task
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

Does SKIP LOCKED FOR UPDATE work with views in this setup?, i.e. Does it guarantee that the same ID will not be picked?

How to test it to be sure?

It does work as expected.

To test it, start two sessions and use explicit transactions, e.g.

Session 1:

BEGIN;
SELECT * FROM outstanding_task WHERE id=1 FOR UPDATE SKIP LOCKED

Session 2:

-- Here SKIP LOCKED is optional.
BEGIN;
SELECT * FROM outstanding_task WHERE id=1 FOR UPDATE SKIP LOCKED

Assuming there is an outstanding task with id=1, then the first transaction should return a row and the second one should not.


Altering Multiple Tables I have database with 170+ tables in mssql 2012. Some of them contain the field "UserID", varchar(9) while others do not. Due to an application redesign, I need to alter all tables in the database to check if the field exists, and if it does, I need to change it to varchar(50). If not, then I need to add it.

Can someone point me how to do this with some kind of batch procedure? I don't have much experience with mssql databases, so detailed explanation would be appreciated.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += [sql] FROM
(
  SELECT [sql] = N'
  ALTER TABLE '
    + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' ALTER COLUMN ' + c.name + ' VARCHAR(50);'
  FROM sys.columns AS c
  INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE LOWER(c.name) = N'userid'
  AND c.max_length < 50
  UNION ALL
  SELECT N'
  ALTER TABLE '
    + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' ADD UserID VARCHAR(50);'
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE NOT EXISTS
  (
   SELECT 1 FROM sys.columns
     WHERE LOWER(name) = N'userid'
     AND [object_id] = t.[object_id]
  )
) AS x;

PRINT @sql;

--EXEC sp_executesql @sql;

If the string is too long to properly validate via PRINT, see this tip for other ideas.

You'll want to also make sure that you update any stored procedures that take this as a varchar(9) parameter, since these will silently truncate, and also any explicit conversions or variable declarations you are doing in ad hoc SQL, views and other modules, etc. For example if you have two users, one with ID = frankenstein and the other just frankenst:

DECLARE @UserID VARCHAR(9) = 'frankenstein';
SELECT @UserID;

Result:

frankenst -- this will match the wrong user!


Querying multiple tables I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together.

I have the following tables/fields:

SR_service: stores ticket data
  SR_Service_RecID
  SR_Status_RecID
  Last_Update
  Date_Req
company: defines a company
  Company_RecID
  Company_Name
SR_type: stores ticket type
  SR_Type_RecID
  Description
SR_Status: stores ticket status
  SR_Status_RecID
  Description
Schedule: the tickets assigned users, and date/time for work*
  RecID (This is the ticketID)
  Member_ID
  Date_Time_Start
  Date_Time_End

*The SCHEDULE table also has a Schedule_Type_RecID, and we are pulling for type '4'. If we join this table with SR_Service, only schedules of type '4' are allowed to be related to tickets in the system.

The first sub-query is for the type of ticket I am looking for. The type of ticket is dependent on what 'board' the ticket belongs to. Leaving out @Board will give me the type of ticket I'm looking for, across all boards.

The second sub-query is making sure I only get tickets that are in NOT closed status. Again, the closed SR_Status_RecID is dependent on the board. I return the closed status of all boards here.

I am at a loss how to get the ticket, company info, schedule info, and ticket status all in one query. The following is the list of what I need: Ticket_ID, status, assigned resources, company name, last update, and required date.

So far, I have come up with:

SELECT s.SR_Service_RecID, s.SR_Status_RecID, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t INNER JOIN SR_Board b
                        ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

This will get me a good amount of data, but there are a few problems:

I only get the status_ID, not the actual description of the status.
I am not quite sure how to add the Schedule table into the query to determine when a person is schedule out to perform the ticket tasks.

This will get you the Status Description:

SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s
    INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
    INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t
                        INNER JOIN SR_Board b
                            ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

To add the schedule, you'd need to add a key field from the schedule table to the SR_Service table.


Is it OK to keep a value which updates in a table? We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc.

Up until now we had a Card entity which has a collection of Account entity, and each Account has an Amount, which updates in every Deposit/Withdrawl.

There is a debate now in the team; someone has told us that this breaks Codd's 12 Rules and that updating its value on each payment is trouble.

Is this really a problem?

If it is, How can we fix this?

Yes, that's non-normalized, but occasionally non-normalized designs win out for performance reasons.

However, I would probably approach it a little differently, for safety reasons. (Disclaimer: I don't currently, nor have I ever worked in the financial sector. I'm just throwing this out there.)

Have a table for posted balances on the cards. This would have a row inserted for each account, indicating the posted balance at the close of each period (day, week, month, or whatever is appropriate). Index this table by account number and date.

Use another table for holding pending transactions, which are inserted on the fly. At the close of each period, run a routine that adds the unposted transactions to the last closing balance of the account to calculate the new balance. Either mark the pending transactions as posted, or look at the dates to determine what's still pending.

This way, you have a means of calculating a card balance on demand, without having to sum up all the account history, and by putting the balance recalculation in a dedicated posting routine, you can ensure that the transaction safety of this recalculation is limited to a single place (and also limit security on the balance table so only the posting routine can write to it).

Then just keep as much historic data as necessitated by auditing, customer service, and performance requirements.


How to select students that failed at least at all courses that student with id = 1 failed? I have the following tables:

STUDENT (student_id, first_name, last_name, birth_date, year , domain)

PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary)

COURSE (course_id, course_name, professor_id)

GRADE (student_id, course_id, grade, date_of_exam)

I have to display the students that failed at least at all the courses that student with id = 1 failed.

What I tried:

SELECT
s.student_id,
s.first_name,
s.last_name,
n.grade,
n.course_id
FROM
    student s
    JOIN grade n ON n.student_id = s.student_id
WHERE
    n.grade <= 4;

...this gets all the students that failed but I don't know how to go from this to all the students that failed at least at all the courses that student with id  = 1 failed. If someone could point me in the right direction I would be grateful!

Additional details

For example: If student with id = 1 failed at courses with ids = 2,3. And we have other two students who failed at courses with ids  = 2,3 (like student 1) and they might fail at other courses too, then I want to display those two students.

Here is one way of getting your desired results

SELECT *
FROM   STUDENT S
WHERE  NOT EXISTS (SELECT g1.course_id
                   FROM   GRADE g1
                   WHERE  g1.student_id = 1
                          AND g1.grade <= 4
                   MINUS
                   SELECT g2.course_id
                   FROM   GRADE g2
                   WHERE  g2.student_id = S.student_id
                          AND g2.grade <= 4)

For each student it checks with MINUS whether there are any courses that student_id = 1 has failed that are not in the set of failed courses for the student under focus.

If there are no such courses then the NOT EXISTS evaluates to true and the student is returned.

student_id = 1 will be returned as they have obviously failed at least as many courses as themselves so you may want to filter that out.


Cannot create stored procedure I have the following piece of statement entered into MySQL5.6 Command Line Client. However, the following error was received. I haven't even been able to add in END// Delimiter; after the select statement.

At the same time, i was wondering after the stored procedure has been created successfully, how do i CALL the stored procedure without the command line but using java codes.

Kindly assist. Greatly appreciated!


How do I fix Uneven TempDB Files? I recently added more CPU cores to a SQL Server and as best practices advise, I separated out the tempDB files into the same number of cores(8).

Also along with best practices, I expanded the size of the new tempdb files, limited auto-growth and shrunk the original files to match the size of the new files.

The problem is, since I was going from a single core to 8 cores, all of the tempdb data remained in the first file, which wouldn't allow me to shrink.  Now I have one tempdb file that is larger than the others and I would like to change that.

I suspect to fix this it will require an outage.

Is there a way to do this without an outage?
Is this problem even worth addressing?  I know what best practices say, but does this instance really cause any practical problems other than ugliness?

EMPTYFILE is just there is transfer all the pages to other files, so you subsequently can do ALTER DATABASE ... REMOVE FILE. This doesn't seem to be what we want here.

I wouldn't worry about this, just let it sort itself at next re-start. Remember that tempdb is re-created based on what sys.master_files says. This is the "template" for tempdb regarding number, placement, size etc of the files.

You adjust the size in sys.master_files using ALTER DATABASE ... MODIFY FILE. If you want help, just use SSMS, tempdb, properties, and adjust the size for any of the tempdb files to a larger size. Then script the command and adjust to your liking. (The reason to not specify a smaller size in the GUI is that it will generate a DBCC SHRINKFILE, which only (tries) to address the current size for tempdb and does nothing for the template. It is perfectly possible to specify a smaller size than current size using ALTER DATABASE for tempdb as it will only affect the "template" and not the current size.


Make Unique combinations using mysql I have two tables - table1 & table2 as below :

table1

id | name
1  | color
2  | size

table2

id | table1id | name
1  | 1        | white
2  | 1        | red
3  | 2        | small
4  | 2        | medium
5  | 2        | large

In both table first id column in primary key. How can i get unique combination from both tables.  Please suggest query to get the result.

expected output:

white       | small
white       | medium
white       | large
red         | small
red         | medium
red         | large

PROPOSED QUERY

SELECT T1.name,T2.name FROM
(SELECT B.name FROM table1 A
INNER JOIN table2 B ON A.id=B.table1id
WHERE A.name='color') T1 INNER JOIN
(SELECT B.name FROM table1 A
INNER JOIN table2 B ON A.id=B.table1id
WHERE A.name='size') T2
ORDER BY T1.name,T2.name;

SAMPLE DATA

DROP DATABASE IF EXISTS rush1312;
CREATE DATABASE rush1312;
USE rush1312
CREATE TABLE table1
(id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (id));
INSERT INTO table1 (name) VALUES ('color'),('size');
CREATE TABLE table2
(id INT NOT NULL AUTO_INCREMENT,
table1id INT NOT NULL,
name VARCHAR(20),
PRIMARY KEY (id));
INSERT INTO table2 (table1id,name) VALUES
(1,'white'),(1,'red'),(2,'small'),(2,'medium'),(2,'large');

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS rush1312;
Query OK, 2 rows affected (0.55 sec)

mysql> CREATE DATABASE rush1312;
Query OK, 1 row affected (0.00 sec)

mysql> USE rush1312
Database changed
mysql> CREATE TABLE table1
    -> (id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO table1 (name) VALUES ('color'),('size');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
    -> (id INT NOT NULL AUTO_INCREMENT,
    -> table1id INT NOT NULL,
    -> name VARCHAR(20),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO table2 (table1id,name) VALUES
    -> (1,'white'),(1,'red'),(2,'small'),(2,'medium'),(2,'large');
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT T1.name,T2.name FROM
    -> (SELECT B.name FROM table1 A
    -> INNER JOIN table2 B ON A.id=B.table1id
    -> WHERE A.name='color') T1 INNER JOIN
    -> (SELECT B.name FROM table1 A
    -> INNER JOIN table2 B ON A.id=B.table1id
    -> WHERE A.name='size') T2
    -> ORDER BY T1.name,T2.name;
+-------+--------+
| name  | name   |
+-------+--------+
| red   | large  |
| red   | medium |
| red   | small  |
| white | large  |
| white | medium |
| white | small  |
+-------+--------+
6 rows in set (0.00 sec)

GIVE IT A TRY !!!


Internals of Windows-to-Windows SQL Server data transfer This is a sort of generic question and I don't require a very specific answer, but let me ask it by describing a scenario.

You connect to a remote db in SQL Server Studio 08, and run a select * insert into a table in a local db. Assume the schemas are identical, this is not a question about SQL syntax or table design.

If, on a linux box, I write an incredibly light weight application that utilizes the Sybase driver (such as Perl and DBI) to copy only that same table. You do this with a select *, and construct one nice multi row insert into a local mysql db.

The times aren't that different until you start scaling up the rowcount many times over. Assume the hardware is identical, and also the mysql config is ultimatly optimized. The windows-to-windows transfer will always be faster. Now, perl's compile time, the little initialization time is negligible as you scale up. They are both socket based data streams.

My question is, plainly, why the SQL Server scenario so much faster?  Is it the way they format data?  Is it Windows' domination of the hardware for maximum optimization?  How does  SQL Server  make so much more use of the bandwidth when it's Windows-to-Windows?

Edit: @gdn I realize its row by row, i guess what im really wondering is how these types of bulk operations are optimized as such. Coming experience with these client query based apps first, im finding hard to imagine how its done so much more efficiently.

There is a difference:

An RDBMS is optimised for such bulk row operations
A client app has to process row by row

You have factors like:

buffering of output while the client consumes it but for an RDBMS this doesn't matter
SQL Server + Windows integration
choice of engine in MySQL


Update Column in table on midnight daily I am designing a simple 1 table database in mySQL that will have two columns. One column will be the name of a specific restaurant on the webpage I will be using this database with. The second column will be number of reservations at each restaurant.

I'm a mySQL rookie and i was wondering if there is a way to set up a script or cronjob that everyday at midnight would run an UPDATE command against my table to reset all reservation values to 0.

You can write a simple script externally or use the MySQL event scheduler. Both will work just fine. The benefit of the MySQL event scheduler is that it will be part of your database backups.


How to Create key (to be used in join) using concatenation? I want to inner join tables in SQL Server 2008, however both tables have been set without Primary Keys. How to create a key using concatenation of existing columns?

I tried the below but it only results in one record & not the entire table.

declare @lookup as varchar(255)
Select  @lookup = accountID + state + product
From Table1
Print @lookup

You don't need to (and shouldn't) concatenate a string just to join on multiple columns.

You can use

SELECT *
FROM   Table1 t1
       INNER JOIN Table2 t2
               ON t1.accountID = t2.accountID
                  AND t1.state = t2.state
                  AND t1.product = t2.product;

Or - if the columns are nullable and you want nulls treated equal.

SELECT *
FROM   Table1 t1
       INNER JOIN Table2 t2
               ON EXISTS (SELECT t1.accountID,
                                 t1.state,
                                 t1.product
                          INTERSECT
                          SELECT t2.accountID,
                                 t2.state,
                                 t2.product);

Similarly you can create a composite PK on all three columns without this repetitious concatenated string.

(Assumes that all are NOT NULL and no duplicates exist)

ALTER TABLE dbo.Table1
  ADD CONSTRAINT YourPK PRIMARY KEY CLUSTERED (accountID, state, product );


MySQL : count occurences on different columns in a same query I've got his table:

+----+------+---------+
| id | user | upgrade |
+----+------+---------+
|  0 |   A  |   NULL  |
+----+------+---------+
|  1 |   B  |   NULL  |
+----+------+---------+
|  2 |   C  |    A    |
+----+------+---------+
|  3 |   A  |   NULL  |
+----+------+---------+

I would like with an unique query to know the total of occurence grouped by user_id but also to know the total of the upgrades by user.

Something like that:

+------+------+---------+
| user |  nb  | nb_upgd |
+------+------+---------+
|  A   |   2  |    1    |
+------+------+---------+
|  B   |   1  |    0    |
+------+------+---------+
|  C   |   1  |    0    |
+------+------+---------+

I have tried this with no luck:

SELECT user, COUNT(*) AS nb,
COUNT(CASE WHEN t.user_id=upgrade THEN 1 END) AS nb_upgd
FROM table t GROUP BY user

SELECT user, MAX(nb) nb, MAX(nb_upgd) nb_upgd
FROM ( SELECT user,
              COUNT(user) nb,
              0 nb_upgd
       FROM t
       GROUP BY user
     UNION ALL
       SELECT upgrade,
              0,
              COUNT(upgrade)
       FROM t
       GROUP BY upgrade
     ) total
WHERE user IS NOT NULL
GROUP BY user;

fiddle


Is data warehouse monitoring same as database monitoring? I have a requirement, where data warehouse population is to be monitored. I am in confusion, and needed a clear picture. Is data warehouse different than database?

eg. For monitoring database we would monitor its availability, tablespace, cache, connections and other advanced metrics.

On what parameters can we monitor data warehouse?

"On what parameters can we monitor data warehouse?".

Exactly the same ones that you use for monitoring an OLTP database. You might want to pay more attention to things like slow query log (MySQL) for a DW, but that should be on the radar for an OLTP system also.

The common "gotchas" may change, but databases all sit on machines with disk, RAM, CPU and network connections. Bottlenecks can and do occur with any/all of these depending on your hardware (notably RAID - most database apps tend to be I/O bound), your application (and its configuration) and your network bandwidth.


Remove Data From Memory when Using SSAS Tabular In-Memory Mode I use SSAS Tabular in In-Memory mode. I also use partitions to divide my data. So I will have one partition per month.

AFAIK, when using In-Memory mode, server keeps processed data in memory forever.

My concern is, if after a couple of years, my data get big so that SSAS ran out of RAM, what should I do?

Is it possible to remove old partitions from RAM?
Can I convert old partitions to use DirectQuery, while newer partitions still use In-Memory mode?

For the "Is it possible to remove old partitions from RAM?" portion of your question:

Yes.

The BOL entry for Process Database, Table, or Partition mentions Process Clear in the "To process one or more partitions" section. Effectively you could issue an XMLA command to Process Clear a specific partition (i.e. an old partition).

Here's a method to get the SSMS GUI to generate the XMLA command to clear a specific partition.

In SSMS, while connected to your SSAS Tabular server, expand the Tables node, then right click any of the tables and select "Partitions..." which will bring up a new window UI.

Then, click the button that looks like 3 green arrows in a circle (Process) which will bring up yet another new window UI.

Next, Check the partition(s) that you want to clear.

Then, in the Mode dropdown, select Process Clear.

Next, click the Script button at the top, and it will generate the XMLA code in a new query window. You'll get something like below with the object names (with seemingly random GUIDs on the end of some of them):

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessClear</Type>
  <Object>
    <DatabaseID>YourTabularDatabase</DatabaseID>
    <CubeID>Model</CubeID>
    <MeasureGroupID>Your Measure Group_06e45707-4027-41d9-8fbd-9217a864d859</MeasureGroupID>
    <PartitionID>Your Partition_06e45707-4027-41d9-8fbd-9217a864d859</PartitionID>
  </Object>
</Process>

You can then save the script and execute it at-will, or copy the script and have an Agent Job run the code as a SQL Server Analysis Services Command type of job step.

For the "Can I convert old partitions to use DirectQuery, while newer partitions still use In-Memory mode?" portion of your question:

Yes.

The BOL entry for Partitions and DirectQuery Mode discusses this some.

In SSDT, within the Partition Manager (Table menu | Partitions), you can set specific Partitions to be either DirectQuery or In-Memory (Set As Sample).
When a partition is set to DirectQuery, it will have (DirectQuery) with the parentheses appended to the front of it.

Incidentally, if you want to know how much memory objects in your Tabular model is using, check out Kasper de Jonge's blog post on What is using all that memory on my Analysis server instance ?


Best way to write SQL Query that checks a column for non-NULL value or NULL I have a SP with a parameter that has NULL as default value and then I want to do a query like this:

SELECT ...
FROM ...
WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND a.VersionId IS NULL));

The WHERE above checks for both a non-NULL value and a NULL value for @VersionId.

Would it be better in terms of performance to instead use an IF statement and duplicate the query into one that searches for non-NULL and another for NULL like so? :

IF @VersionId IS NULL BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId IS NULL;
ELSE BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId = @VersionId;
END

Or the query optimizer makes it essentially the same?

UPDATE:

(Note: I am using SQL Server)

(And as far as I know, using a.VersionId = @VersionId for both cases will not work, will it?)

This pattern

column = @argument OR (@argument IS NULL AND column IS NULL)

can be replaced with

EXISTS (SELECT column INTERSECT SELECT @argument)

This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article:

Undocumented Query Plans: Equality Comparisons

As there are two arguments in your particular case, you can use the same matching technique with @Blah – that way you will be able to rewrite the entire WHERE clause more or less concisely:

WHERE
  EXISTS (SELECT a.Blah, a.VersionId INTERSECT SELECT @Blah, @VersionId)

This will work fast with an index on (a.Blah, a.VersionId).

  Or the query optimizer makes it essentially the same?

In this case, yes. In all versions (at least) from SQL Server 2005 onward, the optimizer can recognize the pattern col = @var OR (@var IS NULL AND col IS NULL) and replace it with the proper IS comparison. This does rely on internal rewrite matching, so there may be more complex cases where this is not always reliable.

In versions of SQL Server from 2008 SP1 CU5 inclusive, you also have the option of using the Parameter Embedding Optimization via OPTION (RECOMPILE), where the runtime value of any parameter or variable is embedded in the query as a literal before compilation.

So, at least to a large extent, in this case the choice is a matter of style, though the INTERSECT construction is undeniably compact and elegant.

The following examples show the 'same' execution plan for each variation (literals versus variable references excluded):

DECLARE @T AS table
(
    c1 integer NULL,
    c2 integer NULL,
    c3 integer NULL

    UNIQUE CLUSTERED (c1, c2)
);

-- Some data
INSERT @T
    (c1, c2, c3)
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2 UNION ALL
SELECT NULL, NULL, NULL UNION ALL
SELECT 3, 3, 3;

-- Filtering conditions
DECLARE
    @c1 integer,
    @c2 integer;

SELECT
    @c1 = NULL,
    @c2 = NULL;

-- Writing the NULL-handling out explicitly
SELECT *
FROM @T AS T
WHERE
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
);

-- Using INTERSECT
SELECT *
FROM @T AS T
WHERE EXISTS
(
    SELECT T.c1, T.c2
    INTERSECT
    SELECT @c1, @c2
);

-- Using separate queries
IF @c1 IS NULL AND @c2 IS NULL
    SELECT *
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 IS NULL
ELSE IF @c1 IS NULL
    SELECT *
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 = @c2
ELSE IF @c2 IS NULL
    SELECT *
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 IS NULL
ELSE
    SELECT *
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 = @c2;

-- Using OPTION (RECOMPILE)
-- Requires 2008 SP1 CU5 or later
SELECT *
FROM @T AS T
WHERE
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
)
OPTION (RECOMPILE);


Alter default privileges in postgres for other roles Let's say I have a role called user1, user2 and postgres.

Is it possible to alter the default privileges while logged in as postgres for objects created by user1 so that user2 can see them?

This is what I currently have, but this only works if I'm logged in as user1:

alter default privileges grant usage on schemas to user2;
alter default privileges grant select on tables to user2;
alter default privileges grant usage, select on sequences to user2;

Is it possible to get this to work while logged in as postgres?

To add default privileges for a different user, use the FOR ROLE clause:

ALTER DEFAULT PRIVILEGES FOR ROLE user1 ...

See the documentation for details.

As to the required permissions to run that statement, the documentation says:

  You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

That means that, unless you are a superuser, you can only use roles in FOR ROLE of which you are directly or indirectly a member.


Does dropping a partition and running update indexes stop DML execution on the whole table? I am looking to drop a partition from a table in the database.
I wanted to know if I run ALTER TABLE DROP PARTITION UPDATE INDEX, would there be any blocking of DML statement executions on the other partitions as the indexes get updated?

Also, I am looking to drop 38 partitions in total in the table. Should I run the update index statement each time I drop a partition, or drop all 38 partitions and then run the update index statement?

P.S. the index is a global one.

As you know, Partition solution announced to manage heavy data without blocking or high IO operation.
There are various scenarios, I'm going to explain you:

1- Clear data from non-active partitions
First you can clear data with Switch out operation, then merge your specific partition, with this, you will drop a partition without any blocking.
You don't need to refresh your Indexes with this solution.
Notice that, if your partition is Active, you have to wait to other session finished first(It's blocking). Actually you've not dropped active partitions.

2- Drop table partitioning
with this operation, you have to recreate table, then you need to recreate your indexes too. But for other situation you don't need to rebuild, reorganize your indexes.
Tip : You have to maintenance data Fragmentation of active partitions, in regular schedule.


Duration Field in Profiler I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query.

My Requirement is simple:
Get all the scripts which takes more than 25 Sec(Time taken for execution of each statement is required).

Hello Senthil Prabhu,

When we are working with SQL Profiler we must keep in mind that we should not

OVERLOAD THE TRACES
There is no easy way to decide which events or data columns you need to capture as part of a trace. It
depends on the problem at hand, and it requires a good understanding of what events are available and
what each one of them does.

As the SQL Profiler is Most powerful yet complicate to analyst tool

When you connect to SQL Profiler you can create a template of T-SQL Duration. and more in Event Selection Tab you can customize the object name and other filter so you will find only desire details.

TSQL_DURATION
The TSQL_Duration template is used to capture all T-SQL statements submitted to SQL Server by
clients and their execution time. It groups the results by execution time duration.

Hope this will help you.

Thank you,
Sam


How to a vertical master detail query? I want product name to display blank field if same product came before .

product_name    | colors_available | items_left
JUMPAN TSHIRT   | red              | 3
                | blue             | 2
                | black            | 9
B.BALL CUP      | red              | 10

can i get this ouput using mysql query?

You probably want to do this on the client side / in your application, not on the database server. This would give your application all information to still reference each row for further action. The unique identifier in your example would be the combination (product_name, colors_available) - this information is lost, when you change your output on the server side.

If you still want to do it in mysql...

Let's assume your_query returns data like this right now:

+---------------+------------------+------------+
| product_name  | colors_available | items_left |
+---------------+------------------+------------+
| JUMPAN TSHIRT | red              |          3 |
| JUMPAN TSHIRT | blue             |          2 |
| JUMPAN TSHIRT | black            |          9 |
| B.BALL CUP    | red              |         10 |
+---------------+------------------+------------+

The following query will suppress output of product_name whenever the current product_name doesn't change to the row before:

SELECT
        -- compare current row's product_name to local variable
    IF( yq.product_name = @last_product_name,
        -- if they match return NULL
        NULL,
        -- if they don't match update local variable to current product_name
        @last_product_name := yq.product_name
      ) AS product_name,
    yq.colors_available,
    yq.items_left
FROM (your_query) AS yq

This will result in the following output:

+---------------+------------------+------------+
| product_name  | colors_available | items_left |
+---------------+------------------+------------+
| JUMPAN TSHIRT | red              |          3 |
| NULL          | blue             |          2 |
| NULL          | black            |          9 |
| B.BALL CUP    | red              |         10 |
+---------------+------------------+------------+

You can change NULL to '' to return an empty string, of course.


"key violation" when trying to import data into MS Access I am trying to import data into an MS Access table from a flat ASCII file.  I've done this many times before but now I am getting a "key violation."  There is only one key in the table and it's an autonumber, so this is not just a simple matter of duplicating previous keys.  I have looked over other potential problems like integrity violations but have found nothing.  So: Does MS Access keep any more detailed records about which field(s) have the problem exactly?  And if so, how to I call this information up?  Thanks.

Addendum:  I kept hitting the import button and trying to think of what else there might be to examine and then, lo and behold, it worked.  I didn't change anything.  It just worked all of a sudden.  Anyone ever hear of this happening?

Perhaps some of the data was successfully imported, and on your subsequent attempts it tries to reimport the whole file, and collides with the existing data?  More generally, are there any records in the target table with which it could be colliding?

If that's not the case, try cutting the file in half.  Can you import the first half?  If so, the problem is in the second half.  Recurse to isolate the problem record(s).


setting timing on not getting time in log I have made the sql query like:

\timing on
\echo
truncate table raw.decrement_m7_temp;

copy raw.decrement_m7_temp FROM PROGRAM 'zcat /path/filename' with(format csv,delimiter ',',header);

delete from raw.decrement_m7_temp where decrement_dt ilike '%dt%';
delete from raw.decrement_m7_temp where msisdn !~ E'^[0-9]+$' OR char_length(msisdn)>12;

I want to track the how much these query is taking time in log.
I am running the following code:

psql -U postgres -d databasename -a -f /path/fimename -L /path/abc.log

While running this query its display the time on black Unix screen but not track the time in log file. How it is possible to track the time in log file?

\timing is a psql command - that is, it acts on the client side.  If you want to know the timing of your queries on the server side (that is, written to the logs), do
SET log_min_duration_statement TO 0;

This will then result in a log entry for each and every statement.  You can disable this by setting it to -1.  To find the timings, you can grep the server log:
grep duration server.log

The issue with psql's -L option is that

-L filename
--log-file=filename
Write all query output into file filename, in addition to the normal output destination.

That is, anything that is not a query output (and not a header, I'd add) won't be redirected into the log file.  You can either use a shell redirect to write everything into the log:
psql [...] > abc.log

Or, if you want to see the output on screen, too, use tee:
psql [...] | tee abc.log


convert string to integer and round I have the below select stmts but I get an error message when i try to combine them. I want to convert diag_1 varchar datatype for example 250.8 to an integer and then round down to the nearest full number eg 250.

Also the data contains values with V25 aswell as numeric.  Is it possible to remove the 'V' from some of the data?

Select encounter_id,diag_1,diag_2,diag_3,
Try_convert(float,[diag_1]) as [float1],
Try_convert(float,[diag_2]) as [float2],
Try_convert(float,[diag_3]) as [float3]
from dbo.encounters;

Select round(float1,1) as roundvalue
  from dbo.encounters;

Lots going on here. Why are you storing numbers in a varchar column? Why do you sometimes have a number that's only kind of a number? You can fix some of these complications of course by cleaning the data before inserting it and using the right data type. In the meantime, something like this is ugly but gets the job done.

DECLARE @encounters table(diag_1 varchar(50));

INSERT @encounters(diag_1) VALUES('240'),('250.8'),('V25'),('V26.2'),('Vurt'),('V303V2');

;WITH x AS
(
  SELECT diag_1,
    adj = CASE -- first, remove the leading V where it exists:
      WHEN diag_1 LIKE 'V%' THEN SUBSTRING(diag_1,2,255)
      ELSE diag_1 END
  FROM @encounters
)
SELECT diag_1, -- now, try to cast to int the remainder, ending right before the decimal
  adjusted = TRY_CONVERT(int,LEFT(adj, COALESCE(NULLIF(CHARINDEX('.',adj)-1,-1),255)))
FROM x;

Results:

diag_1   adjusted
------   --------
240           240
250.8         250
V25            25
V26.2          26
Vurt         NULL
V303V2       NULL


Joining tables with all rows for each unique column value I have 2 tables as follows:

    | A        |
    +----------+
    | ty_date  |
    | ly_date  |
    +----------+

and

    | B           |
    +-------------+
    | locationid  |
    | ty_date     |
    | value       |
    +-------------+

Let's say Table A has ty_date from 1st January till 31st December while Table B has ty_date from 1st January till, say, 1st August for each locationid.

I am trying to join the table in such a way that in Table B each locationid has all the dates from Table A with the field value having null values for all future dates.

I tried FULL OUTER JOIN ON A.ty_date = B.ty_date but I am still missing the grouping logic for each locationid

Any help on what approach I can try?

You can use a recursive CTE to produce the complete list of days for each locationid then join it back to table_b to get your values

Setup the tables:

CREATE TABLE dbo.table_a (
    ty_date DATE NULL,
    ly_date DATE NULL
);

CREATE TABLE dbo.table_b (
    locationid INT NULL,
    ty_date DATE NULL,
    value INT NULL
);

Add 365 entries to table_a, one for each day:

;WITH Dates AS (
    SELECT 1 AS [RowID], CAST('2019-01-01' AS DATETIME) AS ty_date
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM Dates r1
    WHERE r1.RowID <= 364
)

INSERT INTO dbo.table_a(ty_date)
SELECT ty_date
FROM Dates
OPTION (MAXRECURSION 400)

Add entries in table_b for 3 unique locationid's. One entry for each day until Aug:

;WITH Dates AS (
    SELECT 1 AS [RowID], CAST('2019-01-01' AS DATETIME) AS ty_date
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM Dates r1
    WHERE r1.RowID <= 242
)

INSERT INTO dbo.table_b(locationid, ty_date, value)
SELECT 1,
    ty_date,
    RowID
FROM Dates
UNION
SELECT 2,
    ty_date,
    RowID
FROM Dates
UNION
SELECT 3,
    ty_date,
    RowID
FROM Dates
OPTION (MAXRECURSION 400)

Use the below query to return the full 365 days, one row per day per locationid. The Value column is only filled for values that exist in table_b, otherwise they are NULL

DECLARE @MinDate DATE,
    @MaxDate DATE

SELECT @MinDate = MIN(ty_date), @MaxDate = DATEADD(DAY, -1, MAX(ty_date)) FROM table_a

;WITH AllData AS (
    SELECT DISTINCT 1 AS RowID, locationid, @MinDate AS ty_date
    FROM table_b
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, r1.locationid, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM AllData r1
    WHERE r1.ty_date BETWEEN @MinDate AND @MaxDate
)

SELECT ad.ty_date, ad.locationid, b.value
FROM AllData ad
LEFT JOIN table_b b ON b.locationid = ad.locationid AND b.ty_date = ad.ty_date
ORDER BY ad.ty_date, ad.locationid
OPTION (MAXRECURSION 1100)

Note that MAXRECURSION must be equal to or higher than 365 * the number of distinct locationid values in table_b minus 1

The results:

ty_date     locationid  value
-----------------------------
2019-01-01  1           1
2019-01-01  2           1
2019-01-01  3           1
2019-01-02  1           2
2019-01-02  2           2
2019-01-02  3           2
2019-01-03  1           3
2019-01-03  2           3
2019-01-03  3           3
2019-01-04  1           4
2019-01-04  2           4
2019-01-04  3           4
.....
2019-08-31  1           243
2019-08-31  2           243
2019-08-31  3           243
2019-09-01  1           NULL
2019-09-01  2           NULL
2019-09-01  3           NULL
2019-09-02  1           NULL
2019-09-02  2           NULL
2019-09-02  3           NULL
2019-09-03  1           NULL
2019-09-03  2           NULL
2019-09-03  3           NULL
.....
2019-12-28  1           NULL
2019-12-28  2           NULL
2019-12-28  3           NULL
2019-12-29  1           NULL
2019-12-29  2           NULL
2019-12-29  3           NULL
2019-12-30  1           NULL
2019-12-30  2           NULL
2019-12-30  3           NULL
2019-12-31  1           NULL
2019-12-31  2           NULL
2019-12-31  3           NULL

See here for a db<>fiddle demonstrating this.


Mysql: Schema/Query Performance Approach for aggregated mailbox folders I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes:

a) with all messages they receive
 b) all messages they receive filtered by users they are friends with

For all mailboxes the total/unread numbers inside shall be displayed.

This leads me to the need of the following tables:

 - users (id, username,...)
 - friends (user_id, friend_id)
 - messages (id, sender_id, receiver_id, read_status, mailbox_id, ...)
 - mailbox (id, name, owner_id, ...)

My question regarding the perfomance (thinking of a situation where there are many messages and users):

Is is better to calculate the total/unread values for each mailbox for each page view?

Select count(.) as total,
.... ,
Group By mailbox_id, friend_status, read_status

or

Is it better to store these information in the table mailbox in extra fields like total_msg, total_unread, total_friends, total_friends_unread - and update these values upon each event (new message, read message, new friends, friends break up, etc...)?

Would there be a 3rd approach to be considered?

Thanks for help!

The "Best Way" is probably to do it the former way, calculating the accurate count of messages and friends, etc is going to be easier to manage on the application side. Performance will obviously depend on how many messages and friends, some variables on the database server, and indexes.

A third solution would be to use a caching layer (like memcached) to store the values that aren't going to change that much. Memcache can increment and decrement, which will work perfectly for updating your unread counts. Message counts only get updated when new messages are received, friend counts are only updated when new friends are added (and possibly with the added condition of that friend having to send a message). My point is, here, that the values here don't update that frequently, so memcached is a pretty perfect option for a solution here.

That being said, stressing about the performance of that schema early on before you have factual data on how it's being used and how it's performing definitely falls into the camp of "over optimizing."  Implementing approach #2 or #3 is a trivial add once you have the system in place. Build it first, do what you believe is best, don't over optimize or over engineer. Once it's online, monitor performance, and make fixes as you identify bottlenecks.


SQL Server 2016 memory optimised table: Precreated files constantly growing I have a memory optimised table in SQL Server 2016, with file durability enabled. The table is only 100MB.
SQL Server is constantly creating new files on disk, which start off at a few hundred MB, but then grow constantly until the drive runs out of space then they clear down and the process starts again.
The files are growing at about 900MB an hour.
Running the SQL below shows that all the space is in Precreated Data - so it's not even actually storing large amounts of data, just allocating space to use later.

SELECT state_desc, file_type_desc, COUNT(*) AS [count],
  SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type

File growth on the file group is set to unlimited, so I'm assuming I can set a value on this and stop it growing, but I can't find anything to say why it's allocating so much space in the first place. The data in the table does change very rapidly (a lot of inserts and deletes as the data is constantly processed, but it the data that isn't Precreated never gets over 4GB.

Is there a setting in SQL Server that controls this file precreation?
Is it safe to set a limit on the autogrowth for the file group?

Question title says SQL 2014, but question text says SQL 2016. This issue is not necessarily a bug in either version.

Assuming db is in full recovery, in order for file merge to occur, transaction log backups and checkpoints must occur. If running simple, checkpoint alone will take care of file merge and clearing the log. Please see this blog post:

https://blogs.msdn.microsoft.com/psssql/2017/03/06/why-am-i-getting-so-many-checkpoint-files-when-i-have-in-memory-oltp-enabled/

But @Slowry is also correct, in that you must be fully patched.

There is no knob you can turn to affect file precreation, although the required storage footprint is less in SQL 2016. There is no way to control the size of checkpoint file pairs, containers, or the memory optimized file group (in any version of SQL Server that currently supports In-Memory OLTP).


How can I store least used table files to a slower drive? Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?

What you are asking is not really an automatic feature in MySQL 5.6, I could be wrong.
Notwithstanding, you can set it up manually.
Given the following information

Your datadir is /var/lib/mysql
You data is in the Database mydb
You have this InnoDB table

Table Description
CREATE TABLE mytable
(
   if int not null autop_increment,
   ...
   primary key (id)
) ENGINE=InnoDB;

You have a data folder to hold the least often used tables called /data/least
Make sure /data/least is owned by mysql  by running chown -R mysql:mysql /data/least

Here is what you must do manually
USE mydb
CREATE TABLE mytable_least
(
   if int not null autop_increment,
   ...
   primary key (id)
) ENGINE=InnoDB DATA DIRECTORY='/data/least';
INSERT INTO mytable_least SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytable_trash;
ALTER TABLE mytable_least RENAME mytable;

How was mydb.mytable manifested before the change ?

The .frm file is /var/lib/mysql/mydb/mytable.frm
The .ibd file is /var/lib/mysql/mydb/mytable.ibd

How is mydb.mytable now manifested ?

The .frm file is /var/lib/mysql/mydb/mytable.frm
The .ibd file is /data/least/mytable.ibd

You should test this out with a blank InnoDB table and make sure.
Give it a Try !!!