InnoDB internals - checkpoint, LSN, dirty pages I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage.
However there isn't enough information to answer my question.

When I run show global status, I can get Innodb_buffer_pool_pages_dirty and  Innodb_buffer_pool_bytes_dirty.

I want to know where this information comes from. Does any memory (such as buffer pool or information schema) keep this information and just show them when a query comes? Or does a logical object calculate them whenever a query comes? If so, what is the basic information which could be the materials to calculate?

When I run SHOW ENGINE INNODB STATUS\G, mysql shows the following information:

LOG
---
Log sequence number 9682004056
Log flushed up to   9682004056
Last checkpoint at  9682002296

I also want to know where this information comes from. Especially the "Last checkpoint at". As I researched, the log sequence number is related to the "Log Block Header Number" of InnoDB log blocks in the redo log files. Is this right? Then how does MySQL convert it to a log sequence number and how does it get the base information for them?

Your answers are on the source code: innodb_buffer_pool_pages_dirty. Most of these variables are maintained on memory directly or through very simple calculations. There is no danger of durability loss, as the ones that are "essential" for innodb to work can be recalculated from the transaction log or are 0 on start.

Not really, the lsn is just an offset in bytes, it is maintained on memory (log_sys->lsn) and in the case of a failure, it can be recovered from the header of the transaction log (plus pure file offsets). They are changed on commit/log flush/tablespace flush, both on memory and on disk (exact method depends on the durability configuration, buffering type, etc.).

For basic InnoDB stuff, you can check this blog post by the Oracle InnoDB team and/or check the source code for InnoDB.


How to set Proxy Settings when installing Oracle 11g on Windows 7 Ultimate Edition? I have downloaded the file - "win32_11gR2_database_1of2.zip" from oracle site.

While installing Oracle 11g, I find this window in the first place:

What to do?

What values should I put on the dialog box?

Where can I get the related info very concisely?

Your problem is almost certainly that you are entering an email address into the installer to receive information about updates. However this functionality will only work correctly if you also have a valid Oracle Support account.

Since you are in a home environment, I would leave the email address empty. The installer should then proceed without generating any HTTP requests to Oracle's servers.

You will however also have to download win32_11gR2_database_2of2.zip separately and unzip that into the same directory.


What is the significance of the trailing dollar sign `$` on identifiers in SQL Server? Dumping some internal views, such as sys.system_objects as done in this question I see that some identifiers end with a dollar-sign $

CREATE VIEW sys.system_objects AS
    -- cutout
    FROM sys.sysschobjs$ o -- XXX: HERE
    LEFT JOIN sys.syspalnames n
    -- not relevant

And, this works too

SELECT * FROM sys.sysschobjs$;

Even if only by convention, what is the rule here regarding this? Should I be using this in my own code?

Searching I see a lot of stuff about Excel (which is unrelated), and Money literals (which is also unrelated).

I posted another question which I thought was unrelated, but I believe this terminal $ will shed a lot more light on it. Feel free to check that question for an example of some confusion

Even if only by convention, what is the rule here regarding this?

There is nothing stopping you from using a $ in your code... generally, though, using reserved values is frowned upon. Most internal items you'll see will use a $, a % or %%,  # or ##. I'd stay away from using all of those in my user code.

  Should I be using this in my own code?

See above.

  Dumping some internal views [...]

There are various other items you'll see such as OPENROWSET or OPENTABLE. I'll save you from another question, no there isn't a way to see that definition.

You can think of Dynamic Management Views (DMVs) as taking a memory structure and making it a rowset... and literally that's what is being done. There is a difference between an internal base table (which holds metadata about the database objects) and DMVs (which are in memory structures).

DMVs will be helpful to you, internal base tables will not be very helpful as you won't find much (if any) documentation on the table itself or the values inside of it.

  Can you describe the convention amongst $, a % or %%, # or ## that is used internally?

No, I would also not worry about it as it makes no actual difference to you.

  When you say "no there isn't a way to see that definition" you mean the definition for sys.sysschobjs$

Depends, do you see OPENTABLE or OPENROWSET in that definition as I stated above? If not, then you can probably see the base tables over a DAC or DAC + Single User Mode connection.

  What would you call sysschobjs$ you say "internal item" is that the most specific name in the Microsoft parlance?

I would call it an internal base metadata table. You could call it whatever you like, just how "automobile" is also called a "car".

  You described DMVs (which I believe I understand), and Base Tables which I also believe I understand, but not internal items that end in $ or how they relate DMVs and Base Tables.

Some will end in $ some won't, some will have #, some won't. Like I said above, you'll find a variety of items used. There isn't a hard and fast rule. Example being you see that table end in $ but there are functions that also use $. Again, at the end of the day it's a name and nothing else.

I believe you're reading into this waaaaaaaaay too much. There is no secret code or handshake or society.


After trying multiple times and with the help of the answer here, I was able to get the right query:

SELECT t1.`id`, t1.`BU_ID`, t1.`country_id`,
       t1.`Name`, t1.`Description`, t1.`Length`,
       t1.`Width`, t1.`Height`, t3.min_lwh
FROM boxes AS t1
JOIN (SELECT MIN(t2.Length * t2.Width * t2.Height) AS min_lwh
      FROM boxes AS t2 WHERE (t2.Length * t2.Width * t2.Height) >= 3775
   ) AS t3
ON (t1.Length * t1.Width * t1.Height) = t3.min_lwh;


How to model when you could have many (thousands of) attributes to a table (product)? We have a main table, let's say, Product. It has several general attributes like 'Product Name', Manufacturer ID, etc. As of now, all these attributes are implemented in the data model as columns to the Product.

Now we have a new requirement where there could be thousands of attributes. Color, size, weight, dimensions, style, etc. Each of these attributes have pre-defined values. Like color can only be ('green', 'yellow', 'red'). The value set is defined for each attribute. But there could be thousands of attributes and each attribute could have thousands of pre-defined values.

So, for this new feature, we defined these attributes as 'row values' instead of 'columns'.

Something like this:

Product Table

ProductID   Name    Date_Created
1           Candy     some date
2           Shirt     some date
3           Pants     some date

Attribute Table

AttributeID   Name
1             Color
2             Dimension
3             Weight

AttributeValue Table

AttributeValueID   AttributeID    Value
1                  1              Green
2                  1              Yellow
3                  1              Red
4                  2              11x11
5                  2              12x12
6                  2              13x13
7                  3              1 lb
8                  3              2 lb
9                  3              3 lb

Now to create association between Product Table and Attribute table, we have an association table called 'ProductAttributes'

ProductAttributes Table

ProductAttributeId      ProductID      AttributeID      AttributeValueID
1                           1              1                   1
2                           1              2                   4
3                           1              3                   9
4                           2              1                   2
5                           2              3                   8
6                           3              3                   8

Does this model make sense? Could you suggest any improvements? As of now this ProductAttributes Table is expected to grow to 200 Million as there are a more than 100K attributes.

Other points:
1. A product could have more than one attribute
2. A product could have more than one of the same attribute. A shirt is both 'brown' and 'green'
3. All Attributes and its Values have to be pre-defined (loaded from text file)
4. Once the data is loaded (once every few months), there is heavy SELECTing. So the model needs to be read-optimized.

What's the question? Does this make sense? Yea, sure.

1. A product could have more than one attribute Fine
2. A product could have more than one of the same attribute. A shirt is both 'brown' and 'green' Um. If you say so. Sounds a bit strange but that's up to you, I guess.
3. All Attributes and its Values have to be pre-defined (loaded from text file) Again, sounds a bit strange that numerical fields like weight and length have to have pre-defined values. I personally go for a table of pre-defined values and storing non-predefined values separately, but it makes the structure a LOT more complex. Up to you if you want (or need) to do it this way, really.
4. Once the data is loaded (once every few months), there is heavy SELECTing. So the model needs to be read-optimized. If it's properly indexed then the fact that you've got a squillion rows shouldn't impact on performance so much.

Example query to get the product name and attributes for a product:

SELECT
Product.ProductID, Product.Name,
Attribute.AttributeID, Attribute.Name,
ProductAttributes.AttributeValueID,
AttributeValue.AttributeValueID, AttributeValue.Value,

FROM
Product
JOIN ProductAttributes ON Product.ProductID = ProductAttributes.productID
JOIN AttributeValue ON ProductAttributes.AttributeValueID = AttributeValue.AttributeValueID
JOIN Attribute ON AttributeValue.AttributeID = Attribute.AttributeID

WHERE Product.ProductID = 1234

Index-wise, make sure you've got the usual primary key indexes. You'll also need an index on ProductAttributes.productID. You'll need an index on Attribute.name if you're planning on referring to this field in a query condition (eg get products which have a value for attribute 'weight')

You don't really need the AttributeID in the ProductAttributes table, as you've got the AttributeValueID, whose table then links to ProductAttributes. But having it there could conceivably act as a short cut if you were frequently getting a list of Attributes for a product without their values:

SELECT
Product.ProductID, Product.Name,
Attribute.AttributeID, Attribute.Name
FROM
Product
JOIN ProductAttributes ON Product.ProductID = ProductAttributes.productID
--THIS JOIN IS NOW NOT REQUIRED...  JOIN AttributeValue ON ProductAttributes.AttributeValueID = AttributeValue.AttributeValueID
--THIS JOIN CAN BE REPLACED BY THE ONE BELOW...  JOIN Attribute ON AttributeValue.AttributeID = Attribute.AttributeID
JOIN Attribute on ProductAttributes.AttributeID = Attribute.AttributeID
WHERE Product.ProductID = 1234

@Colin'tHart - you may be right about it not being a good design, but you'll need to explain why for your argument to have any weight.


how to prevent replication to stop if there is no activity for some time? On one of my transaction replication publications, when there is no any activity, after some time, the replication stops.

Where can I see these replication settings?

what to put in place to prevent this to happen?

BOL has it documented :

  Subscriptions can be deactivated or can expire if they are not synchronized within a specified retention period.

  If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours) and there are changes in the distribution database that have not been delivered to the Subscriber, the subscription will be marked deactivated by the Distribution clean up job that runs on the Distributor. The subscription must be reinitialized.

  If a subscription is not synchronized within the publication retention period (default of 336 hours), the subscription will expire and be dropped by the Expired subscription clean up job that runs on the Publisher. The subscription must be recreated and synchronized.

  Where can I see these replication settings?

You can see them at distribution database by running sp_helpdistributiondb

  what to put in place to prevent this to happen?

Do it programatically as I have described in my answer here.

or

You can adjust the max_distretention value for sp_adddistributiondb which is 72hrs default to a large value depending on your organization's need.


Is there a performance difference between a Dropped Index and a Disabled Index, for a NON-Clustered Index I'm aware that there are substantial differences between Dropping and Disabling a CLUSTERED Index, but I want to know about NON-CLUSTERED indexes.
And I'm aware that Disabling an Index retains the Metadata of the index definition, making it easier and lower maintenance to reinstate the Index afterwards.
But from a straight "Performance of INSERTs, UPDATEs and DELETEs" point-of-view ...
Is there any performance difference between having Disabled a Non-Clustered Index, and having dropped it entirely. Either during the table edits, or for the CREATE vs the REBUILD afterwards?

As documented:

The query optimizer does not consider the disabled index when creating query execution plans. Also, queries that reference the disabled index with a table hint fail.

The index is completely ignored for querying

The index is not maintained while it is disabled.

So it doesn't appear in inserts, updates and deletes.

You cannot create an index that uses the same name as an existing disabled index.

Not documented, but you cannot drop columns that the index depends on.


mySql while loop ending after a insert I have a stored procedure that is looping through a result set from a select statement. Inside of the loop there is a if statement that has two insert statements. When one of those insert statements runs for the first time i am kicked out of my while loop and the procedure ends. I have tried storing the inserts in another procedure and call that procedure from the inside the if statement and i have tried adding commit after the insert statements. Neither have works.

EDIT: OK i have found the issue. On the select statement at the beginning of the loop i have a select statement that is there to see if we run the insert statements. This statement will some times set a null value to the variable. this seems to be the reason that we are leaving the loop early. I have added this statement into the initial result set pull and it is working well now.

WHILE(curse = TRUE) do
        fetch theTable into ciid,crid;
        SELECT ClientInformation_CIID INTO classAttOrAb FROM clientmonitoringdata LEFT JOIN clientattendancemonitoring
          ON FK_clientmonitoringdata_ClientMonitoringDataID = ClientMonitoringDataID
          WHERE clientmonitoringdata.FK_clientrequirements_ClientRequirementsID  = crid AND
          EntryDate BETWEEN startDate and endDate  AND VoidEntry = FALSE AND clientattendancemonitoring.AttendedClass IS NOT NULL LIMIT 1;
        IF(classAttOrAb = 0) THEN
          INSERT INTO clientmonitoringdata(ClientInformation_CIID,EntryDate,FK_clientrequirements_ClientRequirementsID)
            VALUES( ciid, classDate, crid);

          SET cmid = LAST_INSERT_ID();

          INSERT INTO clientattendancemonitoring( FK_clientInformation_CIID, Absence, AttendedClass, ClassAttended, ExcusedAbsenceReason,
            FK_clientmonitoringdata_ClientMonitoringDataID, FK_clientrequirements_ClientRequirementsID, AbsenceNotes, FK_fxuseraccounts_FXUserAccountsID)
            VALUES(ciid,abType,FALSE,class,exAbReason,cmid,crid,abNotes,empId);
          COMMIT;
        end IF;

        SET classAttOrAb = 0;
      end while;

END


Turning on RCSI on on-prem CRM 2015 We have crm-2015 on-prem, inplace and running and causing lot of deadlocks when someone tries to update anything in batch and same time if multiple users are reading those activities.

We are planning to turn on RCSI. I already did on DEV. server and tested and didn't get any locks, but I know that's not the right and only test.

My questions to all experts here is,

Current size of DB is 40 GB, how hard is to turn that option now to ON.
Once we turn on, can we turn back off using the same procedure stopping all   the services making DB with one active transaction only and then execute ALTER DB command with set option to OFF.
I also read that some code changes requires but in existing application what to inform our developers about changing the code, or it is best to do all operations which we are performing and see if anything breaks.
last but I guess this most important once we turn on does tempdb database version store cleanup  happens by itself or do we have to turn on, if we have to from where?

Please help.

Thanks.

It's critical that you do not enable RCSI on a database without the application vendor and your own developers certifying that they support it. The reason is that the behaviour of all your read queries will change, but not actually break in such a way that replaying your workload will detect. Read section 3 at https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/.

To answer your specific questions:

Switching on will be near-instantaneous (assuming only one DB connection).
Yes, switching off is just as painless.
See above.
Cleanup happens by itself. Section 2 of the post above has some things to consider.


SQL database performance optimization We have a write-heavy SQL database (SQL Server 2008) and the insert speed decreases after some time. I read these similar questions: Sql insert speed up, SQL: What is slowing down INSERTs if not CPU or IO? and Speed up INSERTs.

Also, I read this post about how to analyze SQL Server performance which helped me to know how to find bottlenecks (for example, by querying sys.dm_exec_requests and sys.dm_os_wait_stats), but still I have difficulties in interpreting the query results and fixing the problem.

First, I started with querying the sys.dm_exec_requests which only returned one session id (select query) with status "running" (i.e., I found NO SUSPENDED SESSIONS). So, if nothing has blocked my insert, why does it become slow?

Next, I used sys.dm_os_wait_stats to check statistics about all wait types. The result showed that LATCH_EX, CXPACKET AND PAGEIOLATCH_SH had the most wait_time (694379 ms, 310364 ms and 308335 ms respectively).

Then I used sys.dm_os_latch_stats to find the most prevalent latch type which in my case was ACCESS_METHODS_DATASET_PARENT.

First of all, I don't know how to find the query related to each session_id in the results of querying sys.dm_exec_requests.
Secondly, what numbers of wait-time (in sys.dm_os_wait_stats) should be considered as high? And if the above mentioned numbers (results of sys.dm_os_wait_stats) are high, how can I decrease them?
As far as I understand, ACCESS_METHODS_DATASET_PARENT is related to parallelism and one of the solutions I've found was reducing the degree of parallelism. Is that right?
In MySQL, there are some tunning settings which could be done right after installation (e.g., increasing innodb buffer pool size), is there something similar in SQL Server?

Some more info:

Using sys.dm_db_index_usage_stats, number of reads and writes are
80336 and 70672 respectively.
One of our most busy tables is trans_all (showing all payments
transactions) with NO TRIGGERS, NO CONSTRAINTS (this is the same for
all tables), but it has a CLUSTERED INDEX, namely, PK_trans_all
consist of    the following columns: gs_id(smallint), pt_id(tinyint),
fueling_time(datetime), purchase_type(tinyint).
Database size is 2.6 GB and the size of trans_all table is 155 MB.

UPDATE_1

Regarding the ACCESS_METHODS_DATASET_PARENT, I tried the solution that I've found (i.e., changing the degree of parallelism. I changed it to 1 to make sure that query never go to parallelism), but it didn't fix the issue :| should I change it again? what is the default that I should use?

UPDATE_2

I just checked the size of transaction log file for my database which is 2.4 GB and %log space used is 98%. Could this be the reason to slow down my inserts? Should I increase the log file size?

Also, I used sys.database_files to check the file size, and the amount of empty space for my database. Result shows that filesize is 195 MB and empty space is 0.187 MB.

There are many factors that could be causing this slow down of inserts as you have described.

Something that I do not see in your information is the Database / Table size that the inserts are going into.  As the table grows, the longer your inserts will take due to table size, fragmentation and writing the same data to your indexes.

The DM queries you are running have given you some good information.  I would also recommend using Brent Ozar's toolkit. Link

I would suggest you use SP_BlitzIndex and SP_BlitzCache.  These will give you another view of what is actually happening in the database and what the insert queries you are running are doing at that time.

I would also check when the last time a DBCC CHECKDB was run and also when you last rebuilt your indexes and statistics.

Thanks for the updates, see below:

Some more info:

Not a huge amount of read/writes so I'm not too concerned.

Do you know when the last time the trans_all index was rebuilt?  If you only have one covering index and many inserts, it will take longer each time when inserting data to it if it is out of date.  Below is a query to check fragmentation on your database tables:

Use <Database>
GO

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

UPDATE 1:

MAX DOP settings can affect your query run time, but I can't be sure it is doing so without seeing the query you are using and then looking at the estimated plan results to see what SQL server is using.  There are two statistics to use to find out how the query is affected when adjusting the MAX DOP.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

Here is some information by Kendra Little over at Brent Ozar on using these to give you guidelines.

tsql-measure-performance-improvements

and

q-can-high-maxdop-make-a-query-slower

UPDATE 2:

I would recommend increasing your Log size if it is 98% full.  Either add another trans log or increase it.  You might also look at TEMP_DB and see what it is doing because SQL using that as a dumping ground for many things.

Empty space isn't really an issue unless you are out of room to grow.  It is just telling you how much room is left until the next auto grow.


PostgreSQL compare a timestamp with timezone with a timestamp How do you compare a timestamp with a timezone with a timestamp in postgresql?
I am trying to cast one of the 2 to the type of the other but it does not work for me. Eg.

Having

TS1: 2019-01-07 21:43:00

and

TS2: 2019-01-07 20:43:37+01

I need to compare them and check if they are the same not including the seconds. So I have tried with:

select attimestamp, attimestamp_tz, attimestam=attimestamp_tz::timestamp from attendance where atid=1070

but this will always return false.


SQL Server Extended Event - How to reduce the fields of the event "sql_statement_completed"? My goal: Use SQL Extended Event to perform Audit (strictly using Standard Edition, not Enterprise)

From previous post, I am able to capture and view the sql statement logged by the Extended Event. However, the event has so many redundant fields, such that it makes the .xel file really big (around 4x the original table for 100,000 new inserts)

The image attached shows all the columns captured. As observed, there are numerous fields: attach_activity_id.guid, client_app_name, cpu_time, ... etc
I was thinking this is supposed configure-able. However, it seems that, there is no checkbox in the configure page, as shown below:

Is it possible to reduce the fields? (basically i only need client_app_name and statement)

I'm going to say you are getting all the extra fields because you picked a template when you built your event session (or used the wizard). If you only want certain fields then go uncheck what you don't want. However, there will be some event level info that will always be there, what is shown under the "Event Fields" for each event are their by default and cannot be removed.

Default event for "sql_statement_completed" only captures this, after I checked the global field for client application name:


SQL Server collation mismatch I have database server with collation: SQL_Latin1_General_CP1_CI_AS

I got database backup file which collation is: Latin1_General_CI_AS

When I'm restoring this database and executing some SQL, I'm getting this error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

I've changed database collation to match:

ALTER DATABASE database_name
COLLATE SQL_Latin1_General_CP1_CI_AS

And now getting even more this kind of errors.

Is there any common way to modify all database object collation to prevent this kind of issues?

You have change all user tables and columns individually, droppng and creating all constraints as needed. It's straightforward, just tedious. (SO link)

You can also use COLLATE DATABASE_DEFAULT to coerce collations in code without actually knowing what one is used.


How can you have two auto-incremental columns in one table? I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. I know this is technically not possible, but I guess this is a problem others have tackled before, so I'd like to know if there is a well-known 'solution' for this issue?

What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.

This is my current setup:

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

To check the lateset invoice, I run:

SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'

What you are proposing to do can only be done with MySQL cleanly under three(3) conditions

CONDITION #1 : Use the MyISAM storage engine
CONDITION #2 : Make auto_increment column part of a compound primary key
CONDITION #3 : Each auto_increment for a given type must exist in its own row
See the auto_increment documentation for MyISAM

Here is your original table layout

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

Based on the three conditions I just mentioned, here is the new proposed table layout:

CREATE TABLE `invoices` (
  `invoicenumber` mediumint unsigned NOT NULL auto_increment,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';

Here is an example via sample data and SQL:

drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` (
  `invoicenumber` mediumint unsigned NOT NULL auto_increment,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;

Here it is executed:

mysql> drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)

mysql> create database user1162541;
Query OK, 1 row affected (0.02 sec)

mysql> use user1162541
Database changed
mysql> CREATE TABLE `invoices` (
    ->   `invoicenumber` mediumint unsigned NOT NULL auto_increment,
    ->   `branch` enum('A','B') NOT NULL,
    ->   `date` date NOT NULL,
    ->   `client` varchar(100) NOT NULL,
    ->   PRIMARY KEY (branch,invoicenumber)
    -> ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO invoices (branch,date,client) VALUES
    -> ('A',DATE(NOW()),'John'),
    -> ('B',DATE(NOW()),'Jack'),
    -> ('A',DATE(NOW()),'Jeff'),
    -> ('B',DATE(NOW()),'Joel'),
    -> ('A',DATE(NOW()),'Jane'),
    -> ('B',DATE(NOW()),'Joan'),
    -> ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date       | client |
+---------------+--------+------------+--------+
|             1 | A      | 2012-04-21 | John   |
|             2 | A      | 2012-04-21 | Jeff   |
|             3 | A      | 2012-04-21 | Jane   |
|             4 | A      | 2012-04-21 | June   |
|             1 | B      | 2012-04-21 | Jack   |
|             2 | B      | 2012-04-21 | Joel   |
|             3 | B      | 2012-04-21 | Joan   |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!


what happens when I try to insert data into mysql while table is locked? Simple question but somehow impossible to find a clear answer:

What happens when I try to insert data into mysql while the table is locked?

1) INSERT query fails

2) INSERT query waits until lock is released

3) something else

The "normal" result is the INSERT query waiting until the lock is released. But there are some limits and special cases (you wrote about locking TABLES so MyISAM or explicit LOCK TABLES statement)

the waiting can timeout (lock_wait_timeout)
Concurrent Inserts are possible when you just append to the end of the table, in that case no waiting happens and INSERTs run in parallel. That needs the locking to originate implicitly from other INSERT/SELECT etc or explicitly from READ LOCAL table lock. If you explicitly lock the table without the LOCAL modifier, this feature cannot be used according to the manual.

But nowadays you should mostly use the InnoDB engine and transaction. In that case there are usually no explicit table locks used. There still can be metadata locks. But according to the manual InnoDB will not escalate row locks to table locks, instead using Multiple granularity locking according to https://www.percona.com/blog/2010/06/08/table-locks-in-show-innodb-status/ .
With row locks many operations can run in parallel and inserts should only wait if there is a gap lock on the gap into which the insert should put the inserted row.


Best Data Type to Store Result of HASHBYTES('MD5', ...) What would be the best data type to store the results of the HASHBYTES('MD5', ...)?

It outputs 16 bytes of binary as follows: e.g.

0x5CFCD77F9FF836189D2F647EBCEA183E

I could store it in the following data types:

char(34)
binary(16) (I think - I read here (https://stackoverflow.com/questions/14722305/what-kind-of-datatype-should-one-use-to-store-hashes#16680423) that using the same algo should return the same number of bytes every time regardless of the input string)
other?

Every row will have a value (no nulls), and the column will be used for comparison against a similar column in another table.

Which is the best data type to store HASHBYTES output for use as described above?

I was thinking that since fixed length data types can sometimes be more efficient on joins, etc. binary(16) vs varbinary(8000) (the default output of HASHBYTES) seems best, and binary(16) vs a varchar(34) is better since it would use less storage space.

According to Books Online:

  The output conforms to the algorithm standard: 128 bits (16 bytes) for
  MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32
  bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512.

So despite the fact that the function technically returns varbinary(8000), you'll only ever get 16 bytes with MD5. If you are completely certain you only need MD5, then a binary(16) will do it. If you want to play it safe, and allow any possible algorithm (current or future) that HASHBYTES can handle, go with varbinary(8000).

I definitely wouldn't recommend a char type, since that will involve string conversion overhead, and unnecessary storage overhead.


Streaming replication between postgresql 9.2 and 9.3 Does anyone know if it's problematic to have a postgresql 9.3 slave setup to do streaming replication from a postgresql 9.2 master?

In general, log shipping between servers running different major
  PostgreSQL release levels is not possible. It is the policy of the
  PostgreSQL Global Development Group not to make changes to disk
  formats during minor release upgrades, so it is likely that running
  different minor release levels on primary and standby servers will
  work successfully. However, no formal support for that is offered and
  you are advised to keep primary and standby servers at the same
  release level as much as possible.

It is quite plain in the documentation - you may try but don't be surprised if it does not work.


Azure VM disk expansion online Can we expand the disks in Azure VM without stopping the VM itself. (online)

We are looking for an online method to expand the disk which we are not able to crack. We have certain apps moving to IAAS which at times require disk expansion.

I believe the answer is you cannot. I tried doing so but you get below message on Azure portal which rules out the possibility

Disks can be resized or account type changed only when they are unattached or the owner VM is de-allocated.

So you have to either stop the VM or detach the disk, change its size and attach it again. With VM being online it is not possible.


What would be the best way to design a table in Mysql that handles read items in an RSS feed reader? I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been read. A user can read items, and then they don't appear anymore.

I have a table that handles each item that is read by a user.
Previously it was like this:

id (INT) PRIMARY
user_id (INT)
item_sid (VARCHAR)

My first beginner mistake was to use an id, instead of putting an index on item_sid. id is completly useless, and should not be used.
Also for some queries, to avoid joins, I think it is better if I put in a feed_id column, to grab every item from a specific feed.
The new table should look like that:

user_id (INT)
item_sid (VARCHAR)
feed_id (INT)

Here are some example of queries running on this table:

Get amount of items a user has read from a feed (count(item_sid) WHERE user_id = x and feed_id = x)
Check if an item has been read (WHERE user_id = x AND item_sid = x)
Delete an item that has been read
List all items that have been read by the user (SELECT item_sid WHERE user_id = x)

I have two questions about this:
1) What type of indexes should I put on this table, I have not a very good understand yet of how every indexes work, I'm not sure if I should use fulltext or not on the item_sid
2) Am I doing it the correct way, or is there something better I could do ?

Thank you

This looks like a join table on Items and Users.  You may find putting ID columns these two tables and using them in the join table works well.  Feed would be a parent for Items, and you could make the primary key if Items a compound key including Feed Id.

For the table in question you likely want indexes on:

User_Id, Feed_Id, Item_Id (Primary key searchable for user's data)
Feed_Id, Item_Id  (Foreign key from Items compound key)
User_Id  (Foreign key from Users, optional as included in the first index)


SSDT Schema Comparison for Stored Procedure signed by a Certificate I am using SSDT 14.0.50730.0 in Visual Studio Professional 2015. I am comparing databases between two instances and it is finding that there are stored procedures that do not match with regard to their signatures.

In both instances, the Certificate was created in each instance from the same script and the signatures were also added by CERTIFICATE with a PASSWORD using the same script in each instance.

However, Schema Comparison is showing the procedure in both the source and target as having a CERTIFICATE with a SIGNATURE with Signature values that do not match.The generated publish script drops the SIGNATURE BY CERTIFICATE, modifies the procedure, and then adds a SIGNATURE BY CERTIFICATE WITH SIGNATURE. The signature value matches what was shown in the source instance. However, the script fails with the message "The signature of the public key is invalid."

What do I have to do to get these procedures in sync?

Before getting into any specifics about Certificate creation and module signing, we can at least verify whether or not the two instances have the same certificate. Run the following on both instances:

SELECT [thumbprint], [cert_serial_number], [sid],
       [issuer_name], [subject],
       [expiry_date], [start_date]
FROM   sys.certificates
WHERE  [name] = N'cert_name';

If the Certificates are the same, then all of those fields will have identical values across both instances (or both databases on the same instance). But you mainly just need to check the thumbprint field since it is an SHA-1 hash of the certificate. If that value is different between the two locations, then you aren't dealing with the same certificate.

You say that

  the Certificate was created in each instance from the same script

but do not mention exactly how the Certificates are being created. They can be generated in SQL Server (this is known as "self-signed") by using the following syntax:

CREATE CERTIFICATE [cert_name]
  ENCRYPTION BY PASSWORD = 'certpassword' -- this is optional but best to use
  WITH SUBJECT = 'cert_subject';

This method will generate a new private key (each time it is run) and encrypt that with the password specified in the ENCRYPTION BY PASSWORD clause else encrypted by the database master key (which is why it is better to specify the password).

Or, you can create them by providing an existing Assembly, File, or Binary literal:

CREATE CERTIFICATE [cert_name]
FROM
   ASSEMBLY assembly_name
 | EXECUTABLE FILE = 'path_to_DLL'
 | FILE = 'path_to_file'
   WITH PRIVATE KEY ( FILE = 'path_to_private_key' )
 | BINARY = 0x......
   WITH PRIVATE KEY ( BINARY = 0x..... )

The FILE and BINARY methods allow for optionally specifying a private key file or binary literal. So if using one of those methods along with specifying the private key, or if using the signed ASSEMBLY, then creating a certificate this way should provide you with the same private key each time.

So how do you get the FILE or BINARY values?

FILE:

Once you have created the initial Certificate, export it (including the private key!) using the following:

BACKUP CERTIFICATE [cert_name]
  TO FILE = 'path\to\cert_file.cer'
  WITH PRIVATE KEY
  (
    DECRYPTION BY PASSWORD = 'cert_password',
    FILE = 'path\to\key_file.pvk',
    ENCRYPTION BY PASSWORD = 'file_password'
  );

And in the new place:

CREATE CERTIFICATE [cert_name]
  FROM FILE = 'path\to\cert_file.cer'
  WITH PRIVATE KEY
  (
    FILE = 'path\to\key_file.pvk',
    DECRYPTION BY PASSWORD = 'file_password',
    ENCRYPTION BY PASSWORD = 'cert_password'
  );

BINARY: (only available in SQL Server 2012 and newer)

Once you have created the initial Certificate, get the certificate and private key values using the following:

SELECT CERTENCODED(CERT_ID('cert_name')) AS [CertificateBinary],
       CERTPRIVATEKEY(
                      CERT_ID('cert_name'),
                      'temp_password',
                      'cert_password'
                     ) AS [PrivateKeyBinary];

And in the new place:

CREATE CERTIFICATE [cert_name]
  FROM BINARY = 0x{CertificateBinary}
  WITH PRIVATE KEY
  (
    BINARY = 0x{PrivateKeyBinary},
    DECRYPTION BY PASSWORD = 'temp_password',
    ENCRYPTION BY PASSWORD = 'cert_password'
  );

If you are using SQL Server 2012 or newer, then the BINARY method would be the easiest since it is completely self-contained (i.e. doesn't rely upon any external files).

PLEASE NOTE:

Regardless of which method above is chosen, the initial Certificate creation only needs to happen once, and probably needs to be done manually so that the backup file(s) or binary literals can be incorporated into the SSDT project.
If the private key is being used for creating the Certificate in the target database, then there is no need for the WITH SIGNATURE clause of ADD SIGNATURE since it is only used when the private key was removed or not used in the creation of the Certificate.
In this case (i.e. all of those fields in sys.certificates matching and using the private key in the CREATE CERTIFICATE), signing modules in two different locations that have the exact same definition (as in: byte-by-byte the same), will produce the same signature
If the private key is not being used for creating the Certificate, or has been removed (yes, you can do that), then you need to specify the WITH SIGNATURE clause and pass in the binary literal of the signature that was generated by the same private key and same module definition from a location that at least at one point did have the private key.
This is a more secure set up because it only allows for signing modules that you provide the signatures for. If a Certificate does not have a private key, it can still be used to check the validity of signed modules, but it cannot be used to sign new or changed modules (which is kinda cool).

BUT, if:

the Certificates in both locations are identical, and
the schema compare didn't flag the module definitions as being different, and
ADD SIGNATURE is being used in the destination database using the WITH SIGNATURE clause,
the binary literal being used is the same as found in the source database via:

SELECT OBJECT_NAME(major_id), crypt_property
FROM   sys.crypt_properties;

then you need to check the OBJECT_DEFINITION for that module in both locations. They need to be identical, including casing, etc. You can do a SELECT CONVERT(VARBINARY(MAX), OBJECT_DEFINITION(OBJECT_ID(N'{module_name}'))) just to be sure. It is possible that the schema comparison is ignoring differences in comments and/or the casing of the CREATE keyword at the beginning of the definition and/or some white-space, etc.

Please see the following MSDN pages for details:

CREATE CERTIFICATE
BACKUP CERTIFICATE
CERTENCODED
CERTPRIVATEKEY
CERT_ID
ADD SIGNATURE
sys.certificates
sys.crypt_properties


Allow only find and insert action to mongodb collection I want to create collection. that allow only insert and find operation. i read Privilege Actions in mongodb and i applied that method but all actions are performed like remove and update

i tried following

db.createRole({
  role: "master",
  privileges: [
    { resource: { db : "db_name",collection:"lock" }, actions: ["find","insert"] },
  ],
  roles : []
})

 db.grantPrivilegesToRole("master",[{resource : {db : "db_name",collection:"lock"},actions : ["find","insert"]}],{w : "majority"})

how to allow only insert and find ?

I created role with your db.createRole(..., I didn't "need" to use your grantPrivilegesToRole command. Then I created user

db.createUser({"user":"test2",pwd:"password",roles:["master"]})

and changes to it with

db.auth("test2","password")

If I change to database test, command db.lock.insert({}) fails, as it should be, but if I change database to db_name, insert commands works fine and find command works

> db.lock.find({})
{ "_id" : ObjectId("5ae9a57a119b0cf4316e2964") }

Then If I try to remove that document

> db.lock.remove({})
WriteResult({
    "writeError" : {
        "code" : 13,
        "errmsg" : "not authorized on db_name to execute command { delete: \"lock\", deletes: [ { q: {}, limit: 0.0 } ], ordered: true }"
    }
})

Nor update command works

> db.lock.update({"_id" : ObjectId("5ae9a57a119b0cf4316e2964")},{$set:{"something":"somewhere"}})
WriteResult({
    "writeError" : {
        "code" : 13,
        "errmsg" : "not authorized on db_name to execute command { update: \"lock\", updates: [ { q: { _id: ObjectId('5ae9a57a119b0cf4316e2964') }, u: { $set: { something: \"somewhere\" } }, multi: false, upsert: false } ], ordered: true }"
    }
})

"Stupid" question: Have you remember to enable "authentication" (--auth -parameter) at your mongod? Because if no authentication, no authorization...


SQL Server 2017 - Extracting source and target column names within a string containing column mappings I have the following format string stored in text (could be any number of columns):

col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;...

I'm trying to come up with an elegant way of extracting and isolating all the xxx_source column names and all the xxx_target column names so I could store them in variables and get the following end result:

@Source_Columns = 'col1_source,col2_source,col3_source'
@Target_Columns = 'col1_target,col2_target,col3_target'

At the end of the day, I'd like to perform SELECTs on my source and target columns to perform data compares.

This is what I've achieved so far, but I find it's just too complex for nothing (with a table valued function):

CREATE FUNCTION [dbo].[UF_miscParseStringToTable]
    (
        @list   nvarchar(MAX)
        , @sep  varchar(8)
    )
RETURNS @ts table
    (
        [ID]        int identity
        , [value]   nvarchar(MAX)
    )
AS
BEGIN

    -- Parameters check
    if ((@sep is null) or (datalength(@sep) < 1)) return

    if ((@list is null) or (@list = '') or (@list = @sep)) return

    -- Add path wildcards directly with sep
    -- ?worth it?
    if (left(@sep, 1) <> '%')   set @sep = '%' + @sep
    if (right(@sep, 1) <> '%')  set @sep = @sep + '%'

    -- First first sep
    declare @i int
    set @i = patindex(@sep, @list)

    -- Acc values
    while (@i > 0) begin
        insert into @ts ([value]) values (rtrim(left(@list, @i - 1)))
        set @list = ltrim(right(RTRIM(@list), len(@list) + 3 - (@i + len(@sep) )))
        set @i = patindex(@sep, @list)
    end

    set @list = rtrim(@list)
    -- Insert last value, if any
    if (@list <> '') insert into @ts (value) values (@list)

    return

END

The function above basically takes my mapping string and converts it to a list of column names in a table (see query logic below):

DECLARE @Delim varchar(1) = '|'
DECLARE @Mapping varchar(max) = 'col1_source|col1_target;col2_source|col2_target;col3_source|col3_target'
DECLARE @String varchar(max) = REPLACE(@Mapping,';', @Delim)
SELECT * FROM dbo.UF_miscParseStringToTable(@String, @Delim)

The above resulting query yields the following table:

 ID| value
 1 | col1_source
 2 | col1_target
 3 | col2_source
 4 | col2_target
 5 | col3_source
 6 | col3_target

I could perhaps do a join on the column indexes but, I'm finding it difficult to isolate my source and target fields so that I could perform data comparisons between them.  In addition, I'd like to avoid performing an extra join to a table if I don't have to.

Here are the results desired (to be able to perform the following):

SELECT col1_source, col2_source, col3_source FROM mytable;

SELECT col1_target, col2_target, col3_target FROM mytable;

Any help or ideas would be great!

Shawn

You did not specify your RDBMS - my solution uses SQL Server.

Your question states that you have a formatted string stored in text

col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;...

You further state

  I'm trying to come up with an elegant way of extracting and isolating
  all the xxx_source column names and all the xxx_target column names so
  I could store them in variables and get the following end result:

  @Source_Columns = 'col1_source,col2_source,col3_source'
  @Target_Columns = 'col1_target,col2_target,col3_target'

My solution uses SQL Server 2016 STRING_SPLIT. If you're not up to that version, there are other methods to split the string.

drop table if exists #temp
Declare @String varchar(100) = 'col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;'
----------------------------
Declare @SourceColumns varchar(max)
Declare @TargetColumns varchar(max)

--Split on semicolon and use PARSENAME to extract the source and target column information
SELECT @SourceColumns = isnull(@SourceColumns + ',', '') + SourceColumn
    ,@TargetColumns = isnull(@TargetColumns + ',', '') + TargetColumn
FROM (
    SELECT parsename(replace(value, '|', '.'), 2) AS SourceColumn
        ,parsename(replace(value, '|', '.'), 1) AS TargetColumn
    FROM (
        SELECT *
        FROM String_split(@string, ';')
        ) a
    WHERE value <> ' '
    ) a

SELECT @SourceColumns

col1_source,col2_source,col3_source

SELECT @TargetColumns

col1_target,col2_target,col3_target


Mysl hangs when calling function inside select I have a problem with MySQL. I have a query:

select distinct a.continent_code, a.country_code, a.city_code
from t_lst_ip4 a
where a.idncode = (select f_get_ip4_code(in_ip4_desc));

select f_get_ip4_code(in_ip4_desc)
by itself is a function that always returns -1;

it looks like this:

delimiter $$
use `geoloc`$$
drop function if exists `test1`$$
use `geoloc`$$
create function `geoloc`.`test1`
(a int) returns int
begin
    return -1;
end;
$$
delimiter ;

When running this function separately, it works fine and fast.

When calling mentioned SELECT with just '-1' (that function always returns) it works fine.

I guess it has something to do with calling a function inside select?
separately everything works fine.

I guess function executes as many times as there are rows.

So if I have 3M rows, the function will execute 3M time.

I left only 3K entries in the table and now that select runs fast.

Is this how functions are originally executed inside SELECT?

Consider adding the keyword DETERMINISTIC to the CREATE FUNCTION.


Mysql restore all data from Master MySQl Slave replication server got crashed and I am missing around 60 tables, some table structure and data which present in master and by DB size is more than 25Gb, How I Can restore to resolve issue.
Please help to solve it ASAP.
Thanking you in advance...
Nikhil

You need to restore the data from master.

Take a dump of missing tables from master using
mysqldump  --singl-transaction>missing_tables.sql

Stop the slave.

Restore the dump file in slave using
mysql -uroot -p<missing_tables.sql

Start the replication slave will catchup.


How to create a login and user that can only access one schema in one database I have executed the following:

USE [MyDatabase];
GO
CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase];
CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount];
GRANT EXECUTE   ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];
GRANT SELECT    ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];
GRANT INSERT    ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];

(I am not granting any roles as I do not want this login to be able to access any other schema in this database.)

I run SSMS as MyDomain\MyAccount (run as a different user). I then try to connect to this instance. However, I am getting:

  Cannot open user default database. Login failed. Login failed for user
  'MyDomain\MyAccount'. (Microsoft SQL Server, Error: 4064)

I get the same result if I set the default database to MyDatabase in the Connection Properties.

I ran a Profiler trace and see an error 18456 state 38:

  Login failed for user 'MyDomain\MyAccount'. Reason: Failed to open the explicitly specified database 'MyDatabase'. [CLIENT: MyIpAddress]

That is followed by an error 18456 state 40:

  Login failed for user 'MyDomain\MyAccount'. Reason: Failed to open the
  database 'MyDatabase' specified in the login properties. [CLIENT:
  MyIpAddress]

MyDatabase is online and I have no trouble connecting to it with a different login. What additional permission do I need to grant to MyDomain\MyAccount? Is there something else that I am missing?

Off guess you are missing the connect permission.  I'd always thought that was created with the user but there may be conditions where it isn't added.

Try running

USE [MyDatabase];
GO
GRANT CONNECT TO [MyDomain\MyAccount];

You could also try changing the default schema of [MyDomain\MyAccount]

ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = MySchema;

Lastly you can use my sp_dbpermissions and sp_srvpermissions to review the permissions for the user.

EXEC sp_srvpermissions 'MyDomain\MyAccount'; -- Return instance level permissions
EXEC sp_dbpermissions 'All','MyDomain\MyAccount'; -- Return DB level permissions for all databases


How to create a unique column in SQL Azure Federation I need to create two columns in a federated table where ID is a primary key and EMAIL_ADDRESS can never contain duplicate values. Think of it like a user being able to register only one email address at a time, and each user has a unique ID.

CREATE TABLE dbo.PERSON
(
    ID uniqueidentifier PRIMARY KEY NOT NULL
    , EMAIL_ADDRESS nvarchar(256) NOT NULL
    CONSTRAINT [PK_C1] UNIQUE CLUSTERED
    (
    ID ASC,
    EMAIL_ADDRESS ASC
    )
) FEDERATED ON ([dist] = ID);
GO

CREATE UNIQUE INDEX users_email_address
    ON PERSON(ID, EMAIL_ADDRESS);
GO

This will allow me to create a table, but I can still save duplicate values under EMAIL_ADDRESS.

How can I prevent duplicate email address entries?

You can only have uniqueness validation within a single federation, same as partitioned table allow for aligned uniqueness validation only within a partition (federations don't have an 'unaligned' equivalent). The solution is trivial: create a separate, non-federated,  Azure DB used only to validate email uniqueness.


Extracting Data from multiple archive tables I am developing a report in report builder 3.0 where I am extracting data from a table dbo.odcalls.
The problem is this table is archived on monthly basis to a separate database (HN_Backup), and I need to combine the data from all archived tables and tables which will be generated in future and give the results. I have 2 approaches to do it:

union all tables - the issue will be for archive tables in future as 'if exist' will not work with union.
insert every new row added in odcalls to a different table with only insert/update (not delete).

I would avoid using triggers or replication for this.
I can't make changes to the current structure. I was wondering what would be the best way to move every new entry in odcalls to another aggregated table, from where I will run my report.
Please let me know if there is any workaround.

This is a pretty good case for Distributed Partitioned Views.

It would be fairly easy to implement, especially if the archives have the same schema.


DB2 add user with a password I'm struggling here on how to do this I couldn't find anywhere or won't be able understand the documentation.

So I'm connected as a DBA user on a DB2 client (TOAD for example) and I want to create a new user to the database from this client, I went to the User tab, right click on it and chose the Add User option.

There is no place to add the user password. Even confirming the create command the user appears on the users list.

So I went to the command line tool and try to create a new using the GRANT command as:

GRANT DBADM WITH DATAACCESS WITH ACCESSCTRL ON DATABASE TO USER TEST;

The user gets created (I see it on the user tab on TOAD) but I'm unable to connect with it because I didn't define the password.

So, how can I define a password for newly created user on DB2 that it is not an OS user?

There are no users in DB2 that are not OS users. The client software (not only Toad is guilty of this) makes this a bit confusing: what they show as "users" are actually grants given to the (externally defined) users.

As you have discovered, you can grant privileges to arbitrary users or groups, and no verification is done if those users and groups exist at that time.

However, to connect to the database and exercise the granted privileges you must authenticate first, and for that you must have the user defined in the OS (or another configured authentication service, such as an LDAP directory).


select query on unique column AND alternate column of "aliases" I have a MySQL table (~10,000 rows) with a pk id (int) column and a name (varchar) column (as well as a few other unimportant columns for this question).

The name column contains unique values that are "official" names however some of the items have a commonly known alias, abbreviation or alternate spelling. I'm guessing maybe 10-15%.

I have an existing query to the effect of:

SELECT id, name
FROM items
WHERE name
LIKE '%keyword%'
ORDER BY name ASC
LIMIT 15;

to allow users to search for items with a quick type-ahead widget.

Since some items will have aliases I'd like users to be able to search and get matches based on the alternate names.  My current plan was to add another column to the table called alternates containing delimited alternatives.

e.g. (the delimiter : could be whatever)

  id  |    name               |    alternates
------+-----------------------+------------------------
  1   | Computer              | PC : Mac
  2   | Kraft Dinner          | KD
  3   | Chesterfield          | Couch : Sofa : Sete
  4   | BMW                   | Beamer
  5   | Microsoft SQL Server  | MSSQL Server : SQLServer

and then alter my query to:

SELECT id, name
FROM items
WHERE name LIKE '%keyword%'
OR alternates LIKE '%keyword%'
ORDER BY name ASC
LIMIT 15;

Is this an "accepted" practice or is there a preferred or better performing way to do this?

I apologize if this seems simple to the SQL Pros but Googling for "SQL" and "alias" shows hundreds of non-applicable results (surprise, surprise ;-)

Even though 10,000 rows are not a lot, I would go for a slightly different solution that will perform a lot better.

Keep you master table the same: id, name.

Create a new alias/search table: itemid, alias

Create an index on the alias and a foreign key constraint on the itemid.

Do not have a delimited list of aliases, rather have a row for each individual aliases, as well as the original (authoritive?) name.
Then make the searches on this table and remove the wildcard from the beginning of the like comparison.  Having a wildcard at the beginning of the like value will make the query ignore the index.

So for the example data the you have you would end up with an alias table like the following:

masterid | alias
--------------------------------------
1        | Computer
1        | PC
1        | Mac
2        | Kraft Dinner
3        | Chesterfield
3        | Couch
3        | Sofa
3        | Sete
4        | BMW
4        | Beamer
5        | Microsoft SQL Server
5        | MSSQL
5        | SQLServer
5        | SQL Server

This could then be queried with:

SELECT i.id, i.name, a.alias searchmatch
FROM items i
    INNER JOIN itemaliases a ON i.id = a.itemid
WHERE a.alias like 'keyword%'
ORDER BY i.name
LIMIT 15;


Using aggregate function in custom function I need to calculate the rms (root mean square) for different columns.
To not repeat myself I would like to create a custom aggregate function rms(numeric) which should do the following calculation:

SELECT SQRT(AVG(POWER(v, 2))) AS rms
FROM (VALUES (1), (2), (3), (4), (5)) AS vals (v);

The function should be called like the following:

SELECT RMS(v)
FROM (VALUES (1), (2), (3), (4), (5)) AS vals (v);

In both cases the output should be a floating number. In my example 3.3166....

I found several easy examples on how to create custom aggregate functions, but I could not find an example which explains how to use another aggregate function (in my case AVG()) within the custom aggregate function.

I also found the question (https://stackoverflow.com/questions/30667245/writing-my-own-aggregate-function-in-postgresql), which asks essentially the same thing. But the accepted answer recreates MAX. I would prefer not to recreate existing functions.

You would not use another aggregate function.  You would define your aggregate "from scratch".  I am surprised there is not an existing aggregate that does this, but I have not found one.


How to identify which query is filling up the tempdb transaction log? I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.

From http://www.sqlservercentral.com/scripts/tempdb/72007/
;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

EDIT
As Martin pointed out in a comment, this would not find active transactions that are occupying space in tempdb, it will only find active queries that are currently utilizing space there (and likely culprits for current log usage). So there could be an open transaction but the actual query that causes the problem is no longer running.
You could change the inner join on sys.dm_exec_requests to a left outer join, then you will return rows for sessions that aren't currently actively running queries.
The query Martin posted...
SELECT database_transaction_log_bytes_reserved,session_id
  FROM sys.dm_tran_database_transactions AS tdt
  INNER JOIN sys.dm_tran_session_transactions AS tst
  ON tdt.transaction_id = tst.transaction_id
  WHERE database_id = 2;

...would identify session_ids with active transactions that are occupying log space, but you wouldn't necessarily be able to determine the actual query that caused the problem, since if it's not running now it won't be captured in the above query for active requests. You may be able to reactively check the most recent query using DBCC INPUTBUFFER but it may not tell you what you want to hear. You can outer join in a similar way to capture those actively running, e.g.:
SELECT tdt.database_transaction_log_bytes_reserved,tst.session_id,
       t.[text], [statement] = COALESCE(NULLIF(
         SUBSTRING(
           t.[text],
           r.statement_start_offset / 2,
           CASE WHEN r.statement_end_offset < r.statement_start_offset
             THEN 0
             ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
         ), ''
       ), t.[text])
     FROM sys.dm_tran_database_transactions AS tdt
     INNER JOIN sys.dm_tran_session_transactions AS tst
     ON tdt.transaction_id = tst.transaction_id
         LEFT OUTER JOIN sys.dm_exec_requests AS r
         ON tst.session_id = r.session_id
         OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
     WHERE tdt.database_id = 2;

You can also use the DMV sys.dm_db_session_space_usage to see overall space utilization by session (but again you may not get back valid results for the query; if the query is not active, what you get back may not be the actual culprit).
;WITH s AS
(
    SELECT
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count
          + s.internal_objects_alloc_page_count)
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count
      + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text],
  [statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text],
        r.statement_start_offset / 2,
        CASE WHEN r.statement_end_offset < r.statement_start_offset
        THEN 0
        ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
      ), ''
    ), t.[text])
FROM s
LEFT OUTER JOIN
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

With all of these queries at your disposal, you should be able to narrow down who is using up tempdb and how, especially if you catch them in the act.
some tips for minimizing tempdb utilization

use fewer #temp tables and @table variables
minimize concurrent index maintenance, and avoid the SORT_IN_TEMPDB option if it isn't needed
avoid unnecessary cursors; avoid static cursors if you think this may be a bottleneck, since static cursors use work tables in tempdb
try to avoid spools (e.g. large CTEs that are referenced multiple times in
the query)
don't use MARS
thoroughly test the use of snapshot / RCSI isolation levels - don't just turn it on for all databases since you've been told it's better than NOLOCK (it is, but it isn't free)
in some cases, it may sound unintuitive, but use more temp tables. e.g. breaking up a humongous query into parts may be slightly less efficient, but if it can avoid a huge memory spill to tempdb because the single, larger query requires a memory grant too large...
avoid enabling triggers for bulk operations
avoid overuse of LOB types (max types, XML, etc) as local variables
keep transactions short and sweet
don't set tempdb to be everyone's default database -

You may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over - for example database mail, event notifications, query notifications and service broker all use tempdb in some way. You can stop using these features, but if you're using them you can't dictate how and when they use tempdb.


Run scripts on multiple SQL Server Express instances I am asking for a solution to execute custom SQL scripts on multiple SQL Server Express instances on schedule.

Solutions that I have seen like ApexSQL Propagate and Redgate SQL Multi Script do not have a scheduler and I have more than 170 instances to run periodic jobs on them.

Any advice on how to optimally execute scheduled scripts will be deeply appreciated.

you can use powershell script and csv file, to automate most of your work

$csv = Import-Csv C:\sql\db.csv -Delimiter ";"

foreach ($line in $csv) {

    $DBServer = $line.DBServer
    $DBName = $line.DBName
    $CommandText = $line.CommandText

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Data Source=$DBServer;Database=$DBName ;Integrated Security=True;"
    $sqlConnection.Open()

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection = $sqlConnection

    $sqlCommand.CommandText = $CommandText
    $sqlCommand.ExecuteNonQuery()
    $sqlConnection.Close()
    $sqlConnection.Dispose()
}

And, sample csv file for it:

DBServer;DBName;CommandText
.;db;BACKUP DATABASE [db] TO  DISK = N'd:\backup\db.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
ACER;db2;BACKUP DATABASE [db2] TO  DISK = N'd:\backup\db2.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

This script connect to server instance DBServer, in my case to default, and to db, for backup it can be master, and execute CommandText

It use integrated security, but you can extend script to use sql server password security, you can extend script to take sql commands from file, etc, etc, etc..

You can add all your 170 instances into csv file, and script will execute sql commands against all your servers.

Of course, for production, script must be more complicated, with error logging.


SSIS Validation slow. OLTP OLAP running on same server I have four identical servers 2 are used in production and the other two are demo machines/emergency fail over boxes. The more powerful machine a 16 core 127 gigs of Ram hosts both our Datawarehouse and our OLTP database. The less powerful box is an 8 core 98 gigs of ram box that we use for ETL and our datastore. The problem I am having is on the production servers integration services takes a large amount of time to perform package validation. For a query that returns in 6 seconds it takes 40 seconds to complete package validation. As you can see it's a fairly simple package.

Keep in mind this is just the package I'm using as an example all of my packages have the same problem although some to a lesser degree. When I run this the validation portion takes 40 seconds on average.

In order to better test this I ran the exact same package on the sister box which runs all of the same SQL jobs as this box. Only to have the pre-execution validation take 0 seconds and the query take between 4 and 6 seconds.

To further test this I ran the query against both instances of SQL server.

SELECT '{' + CAST(Store_Id AS CHAR(36)) + '}' AS Store_Id
      ,'{' + CAST(ItemGroupDetail_Id AS CHAR(36)) + '}' AS ItemGroupDetail_Id
      ,ItemGroupType_Id
      ,'{' + CAST(Concept_Id AS CHAR(36)) + '}' AS Concept_Id
      ,'{' + CAST(Company_Id AS CHAR(36)) + '}' AS Company_Id
      ,'{' + CAST(ItemGroup_Id AS CHAR(36)) + '}' AS ItemGroup_Id
      ,GroupName
      ,Sort
      ,NameOverride
      ,'{' + CAST(ParentGroup_Id AS CHAR(36)) + '}' AS ParentGroup_Id
      ,Active
      ,'{' + CAST(UserCreated AS CHAR(36)) + '}'  AS UserCreated
      ,'{' + CAST(UserModified AS CHAR(36)) + '}'  AS UserModified
  FROM bos_vAllItemGroups

Execution on the main server with live up to date date returns 63326 rows in 5 seconds on average and the 50527 rows in 3 seconds. Which is to be expected this server has much less activity with the same specs I would expect it to be slightly faster. What I wouldn't expect is for package validation time to increase so much more than actual query run time.

After much digging yesterday I think I may have discovered the problem but am not sure how to prove it. The production OLTP database manages content for digital menu boards across the country that are calling home constantly to check for new content. Most of the time there is nothing to change and the queries to find out finish in milliseconds. However they do send about this process ends up running on average 4500 queries per second. Could this be the cause of my problem? The large volume of transactions coming from our web-server. If so how do I prove it? I have already been trying to get these databases running on separate machines as this OLTP database is growing very quickly as is our datawarehouse and from all my reading keeping your OLAP and OLTP databases on the same server is considered bad practice.

With some offline conversations, Zane was able to determine that if they could keep a connection open, it would hasten the validation.

To that end, I suggested they change the property on the Connection Manager to flip the RetainSameConnection property to True from its default.


How to make trigger function to make daily entering-exiting report? I have a table called daily_report that register what time/date worker goes out from the building and what time/date he/she comes in.

CREATE TABLE daily_report (
        id serial,
        worker_id integer,
        in_or_out integer,
        the_time time,
        the_date date
        );

I assigned "in" status to 1, "out" to 0. I need a query that shows when the worker first enter the building and when he/she exits it in the specific day.(notice some workers may enter the building  and exit next day, like night guards) . Like :
Select min(time), max (time) group by date........
I tried to join table to itself but cant get a result.
Trigger function also will be ok that records daily report in a new table like worker with id 3 enters 08:00 am, exits 18 00 pm in '2016-01-01' for example

please help me to do that.

Let's assume every "in" is followed by an "out" event. In that case you can do:

SELECT worker_id,
    the_date as in_date,
    the_time as in_time,
    lead(the_date) over w as out_date,
    lead(the_time) over w as out_time,
    -- Check if it is really an out event
    (lead(in_or_out) over w) = 0 as out
FROM daily_report
-- Filter for in events
WHERE in_or_out = 1 AND worker_id = [your_worker_id]
-- specify the window function
WINDOW w AS (order by the_date, the_time)
GROUP BY worker_id;

You didn't write which PG version do you have. A better approach is to have LATERAL JOINs (available since 9.3):

SELECT worker_id, i.the_date, i.the_time, o.the_date, o.the_time
FROM daily_report i
LEFT JOIN LATERAL (
    SELECT r.the_date, r.the_time
    FROM daily_report r
    WHERE
        in_or_out = 0 AND r.worker_id = i.worker_id
        (
            (r.the_date = i.the_date and r.the_time > i.the_time) OR
            (r.the_date > i.the_date)
        )
    ORDER BY 1, 2
    LIMIT 1
) o ON true
WHERE in_or_out = 1 AND worker_id = [your_worker_id]

This is a more generic query and will give you back better results if the data is somehow not completely correct.


Subtract values from SELECT in SQL I have the following 2 queries.:

shipManagementInvoiceNetValue:

SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
FROM tckopf AS shipManagementInvoice
WHERE shipManagementInvoice.referenzid = 1
    AND shipManagementInvoice.btyp = 5

shipManagementCreditNoteNetValue:

SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
FROM tckopf AS shipManagementCreditNote
WHERE shipManagementCreditNote.referenzid = 1
    AND shipManagementCreditNote.btyp = 6

but I need the result shipManagementInvoiceNetValue - shipManagementCreditNoteNetValue. I'm using a HXTT JDBC DBF driver that supports more than SQL92.

How can I realize that?

try this..

SELECT X.shipManagementInvoiceNetValue - Y.shipManagementCreditNoteNetValue
FROM
(
   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
   FROM tckopf AS shipManagementInvoice
   WHERE shipManagementInvoice.referenzid = 1
   AND shipManagementInvoice.btyp = 5
)X,
(
   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
   FROM tckopf AS shipManagementCreditNote
   WHERE shipManagementCreditNote.referenzid = 1
   AND shipManagementCreditNote.btyp = 6
)Y


Altering multiple tablespaces at once in Oracle I need help in altering multiple tablespaces at once in Oracle Database 11g.
My particular scenario is that this command will be used in a shell script to set all of the tablespaces in read-write mode for upgrade purposes.
The problem is that each server has a different number of tablespaces, so i can't hardcode the names right in. To solve this I will make a list of all the relevant tablespaces by using the datafiles and I want to pass this list it as an argument.

For instance, you alter one tablespace as such :

ALTER TABLESPACE DATA1 READ ONLY;

I want to do something of the likes :

ALTER TABLESPACE DATA1,DATA2,DATA3 READ ONLY;

Is this possible? If so what is the exact syntax.
Thanks in advance!

The syntax for the alter tablespace statement only supports one tablespace at a time, you can't list more than one as you're trying to.

If you're scripting that command already, simply have your script issue multiple alter tablespace statements rather than one.

If you're generating your list of tablespaces from dba_tablespaces, you can dynamically alter the tablespaces from that with something like:

begin
  for tbs in (select tablespace_name from dba_tablespaces where [your conditions])
  loop
    execute immediate 'alter tablespace '||tbs.tablespace_name||' read only';
  end loop;
end;
/


Estimated plan generation succeeds for batch creating and using #temp table but fails with a permanent table In reviewing the estimated execution plan creation process, I came across this section in SQL Server Execution plans by Grant Fritchey, He mentions:

  The optimizer, which is what generates estimated execution plans,
  doesn't execute T-SQL. It does run the statements through the
  algebrizer...that is responsible for
  verifying the names of database objects. Since SQL Server has not yet
  executed the query, the temporary table does not yet exist. This is
  the cause of the error. Running this same bit of code through the
  actual execution plan will work perfectly.

In the book he references this code:

CREATE TABLE TempTable
(
Id INT IDENTITY(1, 1)
);

INSERT INTO TempTable
DEFAULT VALUES

SELECT *
FROM TempTable;

and the Error you will receive is

  Msg 208, Level 16, State 1, Line 7 Invalid object name 'TempTable'.

To me, this wasn't a temporary table but instead an actual table. So when I tried with a temporary "#" table. I found that the estimated execution plan generated without issue. The estimated plan that was generated is shown here.

Create table #temp
(
ID INT IDENTITY (1,1)
)

Insert into #temp
DEFAULT VALUES

SELECT *
FROM #temp

Can someone explain why there is this difference? Shouldn't the #table not be generated?

Fritchey, G. (2012). SQL Server Execution Plans. Springfield, USA: Simple Talk Publishing.

In SQL Server 2000 the #temp table version does fail with the message

  Invalid object name '#temp'.

However since SQL Server 2005 compiling a plan with a local temporary table does actually create the temporary table behind the scenes.

You can see this by getting the estimated plan for

Create table #temp
(
ID INT IDENTITY (1,1)
)

Insert into #temp
DEFAULT VALUES

SELECT *
FROM #temp
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 3604);

DROP TABLE #temp

Which produces an output like

The negative object id is the id of the object fleetingly created. Polling tempdb.sys.tables in a loop whilst generating the estimated plan and comparing with the TableID subsequently output can show this.

The estimated plan you see for the statements referencing the temp table is of limited use however as even the addition of a single row to the temp table will trigger a statement level recompile and potentially a different plan when you actually execute the SQL.

This behaviour does not apply to global temporary tables or permanent tables.

I presume this is also the same reason as

/*Works fine (assuming T doesn't exist)*/
IF 1 = 1
  SELECT 1 AS X
  INTO   T
ELSE
  SELECT 1 AS X
  INTO   T

/*Fails (There is already an object named '#T' in the database.)*/
IF 1 = 1
  SELECT 1 AS X
  INTO   #T
ELSE
  SELECT 1 AS X
  INTO   #T


How can I apply heuristics to optimise an expression tree? I've converted the following SQL query:

SELECT name
FROM dog d, works_at w
WHERE (d.owner_name = w.person_name) AND (w.kennal_name = "Breeders");

into the following expression tree:

My main question is how does one apply heuristics to optimise an expression tree?

And is it OK to use the abbreviaions (e.g. dog as d) in the query tree, or do I have to write out dog.owner_name in place of d.owner_name?

I've just figured out how to do this, so I'll answer it in case anyone else has a similar problem.

Introduced SELECTs as far down the tree as possible, to reduce the number of comparisons needed.
Introduced PROJECTs as far down the tree too, to reduce the number of columns.
Used a JOIN wherever a CARTESIAN PRODUCT was followed by a SELECT. (There weren't any in my tree but this is what's done apparently).

My final tree looks like this:


How to Backup to Azure Storage? A day before I tried to back up our ddbb log file to azure storage. So, the transact is simple

backup [log|database] <name>
To URL = <Azure_Storage_url>

Credentials are defined apart,

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <Key>

CREATE CREDENTIAL <name_credentials>
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
    ,SECRET   = <SAS_Token>
go

I got the error below:

Msg 3201, Level 16, State 1, Line 34
Cannot open backup device <Azure_Storage_url>. Operating system error 50(The request is not supported.).

I tested that credentials with a bulk insert on the same container I was pointing with  it works fine.

The SAS token was generated with full permissions

No policy were made on the container since I understood (Is my fault here?) that SAS token already had full access.

How can I do a successful backup from scratch?

Thanks!

No policy were made on the container since I understood (Is my fault here?) that SAS token already had full access.

Backup to URL requires a SAS Token generated from a Container Access Policy with fixed start and Expiration Dates.  An Storage Account-scoped SAS token will not work.


I need the reason why we should not install SQL server on the domain controller I am a system administrator for a small firm. Now we consider to rollout Windows Server 2008 R2 for the firm. We plan to implement Active Directory. We would also like to upgrade RDMS, from SQL server 2000 to SQL Server 2014.

We can afford to buy just one server due to size of our organization, so we plan to implement both of AD and SQL Server 2014 on the same machine. However, Microsoft recommends not to install SQL Server 2014 on a domain controller.

I have checked the reasons, but I don't understand what is the problem, so if someone answers my questions bellow, it's really helpful.

You cannot run SQL Server services on a domain controller under a
local service account. →If we run SQL Server on a domain controller
under a domain user account, what would happens?
After SQL Server is installed on a computer, you cannot change the
computer from a domain member to a domain controller. You must
uninstall SQL Server before you change the host computer to a domain
controller. →We plan to promote our server to a domain controller
before installing SQL server 2014 and don't plan to de-promote to
member server because it's our only server. Is our plan wrong?
After SQL Server is installed on a computer, you cannot change the
computer from a domain controller to a domain member. You must
uninstall SQL Server before you change the host computer to a domain
member. →We don't plan to de-promote to member server because it's
our only server. Is our plan wrong?
SQL Server failover cluster instances are not supported where
cluster nodes are domain controllers. →We don't plan to install
failover cluster, so we think that it's not problem. Are we wrong?
SQL Server Setup cannot create security groups or provision SQL
Server service accounts on a read-only domain controller. In this
scenario, Setup will fail. →Because We have just one server, so it
never be a read-only domain controller, so we think that it's not
problem. Are we wrong?

Actually, I guess if we clear (1), we can install SQL server 2014 on our domain controller.

Thanks for reading all of sentences. I hope someone give us answers.

The points you have listed are shown in BOL here, and even though it quotes:

  SQL Server Setup will not block installation on a computer that is a domain controller

It states on this KB article that the setup will fail, although the applies to only list up to 2012 version.

One main reason you are going to have against doing this is Microsoft Support. As it states in documentation you should not install it, they are not likely going to provide support. Especially if you experience any security or performance issues. Unless you fork over a good bit of money they are probably not going to do much for you.

I have never seen a company put these two roles together out of choice. I hate to say it but if they need to run an application in SQL Server and it is going to be a mission critical application there is no reason for them not to invest in running that application. Hardware is cheap these days and it does not take much for AD to run if your organization is that small. You could even go and get 2 or 3 year old hardware for that matter.

Now with that said, Window Small Business server used to be the one stop shop for a situation like you describe. It is an AD, Exchange, and web server all on in one; and if I recall also had an installation of SQL Server but I can't remember. I setup one of these servers for a department one time.

However with Window Server 2012 they changed this to Window Server Essential Edition, and I am not sure what roles you get with this Edition. I would suggest though you simply spend the money to get Standard Edition. A feature you get with Standard is licensing to run two guest OS virtual machines via Hyper-V. If you size the hardware appropriately it will allow you to run your SQL Server instance in one VM and then your application in the second VM. Although your performance is all on one server it provides a boundary separation and would be a fully supported setup by Microsoft. Just my two cents.


I am getting this error : "Error 102 : non-boolean type specified in a context where a condition is expected" for this request : private void button2_Click(object sender, EventArgs e)
{
    con.Open();
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "delete from MYDB where First name='" + first.Text + "'";
    cmd.ExecuteNonQuery();
    con.Close();
    disp_data();
    MessageBox.Show("Contact deleted successfully");
}

You can solve it by putting First name between brackets:
delete from MYDB where [First name]='" + first.Text + "'";

create table t (id int, [First name] varchar(100));
insert into t values (1, 'John Doe');
GO
delete from t where First name = 'John Doe';
GO

Msg 4145 Level 15 State 1 Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'name'.

db<>fiddle here


Calling Sproc From Another Server - runs as SA I have a sproc on ServerA that calls another sproc on ServerB

The servers are connected through linked server.

When the sproc gets called on ServerA it is called by the user login. However, then on ServerB, the 2nd sproc is called by SA.

I would have thought that the user credential would have passed through to ServerB rather than SA?

If it runs as sa on ServerB via a linked server when the user is logged into ServerA as a user login, then the sa account is configured in the linked server properties. If that's not what you want, then fix the linked server so that it uses the current security context instead.


Subtracting sum of previous rows I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause.

I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total field minus Quantity field.
This is a structure table.

And this's a result.

This is my code but it's error at ORDER BY.

select t2.BaseRef 'PO',t2.BaseQty 'qty', t1.NumAtCard 'INV',
        t2.Quantity 'Quantity',
        t2.BaseQty - SUM(t2.Quantity) OVER (ORDER BY t1.DocDate) AS 'total'
from OPDN t1
        INNER JOIN PDN1 t2 ON t1.DocEntry = t2.DocEntry

Can I use other way for sort this data?

If SUM() OVER() is not supported, you may try with the following approach. Rows are ordered by INV number as text (INV100, INV105, ...), not as number (100, 105, ...).

Input:

CREATE TABLE #PO (
    PO varchar(10),
    DocEntry int,
    Qty int
)
CREATE TABLE #INV (
    INV varchar(10),
    DocEntry int,
    Qty int
)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('A123', 1, 500)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('B123', 2, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV100', 1, 100)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV102', 2, 10)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV105', 1, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV106', 2, 190)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV112', 1, 200)

Statement:

SELECT
    p.PO,
    p.Qty,
    i.INV,
    i.Qty AS Quantity,
    Total = p.Qty -
            (
             SELECT SUM(rt.Qty)
             FROM #INV rt
             WHERE (rt.DocEntry = i.DocEntry) AND (rt.INV <= i.INV)
            )
FROM #PO p
LEFT JOIN #INV i ON (p.DocEntry = i.DocEntry)
ORDER BY p.DocEntry, i.INV

Output:

PO      Qty INV     Quantity    Total
A123    500 IV100   100         400
A123    500 IV105   200         200
A123    500 IV112   200         0
B123    200 IV102   10          190
B123    200 IV106   190         0


Insert multiples rows/columns - PostgreSQL 9.2 I've got a table that has several columns, But I need to insert data into two columns of that table.
I need to add data into those columns, from a select statement.

Would it be something like:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'
SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Dson%'

It has to be in the same transaction because the mobiuser_id must go to the selected billable_id on the first select.

How can I do that?

  1 - Select billable_id from dm.billable

  2 - Select mobiuser_id from
  ja_mobiusers

  3 - Insert the billable_id and the mobiuser_id to the
  dm.billables_links table.

I've a CONSTRAINT on the target table: (That's why I need to get it done at the same time. So I won't get errors like:

  ERROR:  new row for relation "billables_links" violates check
  constraint "cc_one_and_only_one_target"

ALTER TABLE dm.billables_links
  ADD CONSTRAINT cc_one_and_only_one_target CHECK ((("customer_id" IS NOT NULL)::integer + ("role_id" IS NOT NULL)::integer + ("mobiuser_id" IS NOT NULL)::integer) = 1);

This is related to my previous question - Which I've asked to be deleted because have done the wrong question and it has been confused.
INSERT INTO + Selects - PostgreSQL 9.2

UPDATE 1: This is an example of what I need

1 - select the billable_id: (SELECT1)

SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'

2 - select the mobiuser_id: (SELECT2)

SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Dadryl%'

3 - Insert those two data into the dm.billables_links table (EXAMPLE):

INSERT INTO dm.billables_links (billable_id, mobiuser_id) VALUES (**SELECT1**, **SELECT2**);

If it's guaranteed (like you commented on your previous question) that each SELECT returns exactly 1 row, the simplest way to combine both would be to just nest one SELECT within the other:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id
    , (SELECT id
       FROM   public.ja_mobiusers
       WHERE  name_first LIKE 'Alisha%'
       AND    name_last LIKE 'Dson%') AS foo  -- alias irrelevant
FROM   junk.wm_260_billables2
WHERE  info ILIKE '%Alisha%';

If there can be 0 - n results per SELECT, it gets more complex. You would have to define what the result should look like for each possible combination.

Note that you get no row (nothing inserted) if the outer SELECT finds nothing, but a NULL value for the second column if the nested SELECT finds nothing.

Or you could just use two subqueries:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
VALUES (
  (SELECT billable_id
   FROM   junk.wm_260_billables2
   WHERE  info ILIKE '%Alisha%')
, (SELECT id
   FROM   public.ja_mobiusers
   WHERE  name_first LIKE 'Alisha%'
   AND    name_last LIKE 'Dson%')
   );

Now you get NULL for each column where nothing is found, and an error if more than one row is found.

I seriously doubt that an ILIKE expression can guarantee a single result row.

Aside: your constraint would be faster and cleaner this way - though more verbose:

ALTER TABLE dm.billables_links ADD CONSTRAINT cc_one_and_only_one_target
CHECK (customer_id IS NOT NULL AND role_id IS     NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS NOT NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS     NULL AND mobiuser_id IS NOT NULL)


MySql - filter bad words in multiple tables using triggers I have for example two tables blog_posts and image and each record in these tables have a text field, that may contain a bad word in it.

so the idea is, I want to check these records and change these bad words with a couple of stars (like ****) the number of stars is fixed and not dependant on words length.

and I have a bad words table called slang.

so what I have so far is like this:

I have two trigger for each table (blog_post and image), one for update and one for insert. so the bad words will filtered out on insert or update. the content of each trigger is the same as below:

BEGIN
  DECLARE i INT;
  DECLARE s VARCHAR(1000);
  DECLARE r VARCHAR(1000);
  SET i = 1;
  SET s = '';
  REPEAT
    SET s = (
      SELECT REPLACE(split, COALESCE(slang, ''), '***') FROM
        (SELECT strSplit(new.text, ' ', i) AS split) s
        LEFT JOIN slang w ON s.split = w.slang
      LIMIT 1
      );
    SET r = CONCAT_WS(' ', r, s);
    SET i = i + 1;
    UNTIL s IS NULL
  END REPEAT;
  SET new.text = r;
END

the strSplit procedure is defined like so (based on a StackOverflow or maybe a StackExchange question I don't remember it):

BEGIN
  DECLARE output VARCHAR(1000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END

so I want to make a trigger to update the blog posts and images text field each time we add or edit a record from slang table. so if I add someSlang into slang table, all images and blog posts that contain someSlang in their text field should get updated and place **** for each instance of someSlang.

I'll appreciate any help and hints.

The slangs table might have a few hundred entries. Whereas the blog_posts and image table might have a lot more data(in the range of millions depending on the traffic). So if an insert or update triggers an update in the other two tables, it might create an unwanted spike in database activity. A better approach would be to plan a Scheduler to do this task.


SQL Server Slowest Query is NULL I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server.
Is there any way I can find out more about this NULL query and why it might be so slow?
Is this some internal query? It doesn't seem like this should be showing up in the report if so.

This is the query which is also showing NULL as the slowest query on my server:
select
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text
from
    sys.dm_exec_requests as r
cross apply
    sys.dm_exec_sql_text(r.sql_handle) as t

How can I find out what this query is and why it's so slow?

That's a system session.
DBCC INPUTBUFFER(37); might give a clue what it's doing, or CROSS APPLY with  sys.dm_exec_input_buffer(), but you might not be able to understand any more about what it's doing. Since it's a background task, it is unlikely to be a cause for any other perceived slowness but, being internal, I'm not sure what you could possibly do to tune it anyway...
If you're just randomly checking dm_exec_requests you should probably limit it to non-system sessions (join to sys.dm_exec_sessions where is_user_process = 1). Why it's not filtered out of the report, I have no no idea, I tend to not use built-in reports because I (and vendors) build much better versions.