Filter rows by jsonb key mask 1. I have table UserData with following schema:

ID - bigint
UserID - bigint
Data - jsonb

2. Data column jsonb has following structure:

date - contain timestamp
text - contain description
photo_* - contains photo's link (sometimes absent in json)

3. Key photo_*:**

In photo_* key of jsonb Data field there are could be different values instead of asteriks (*), e.g.: photo_640, photo_1024, photo_1600

4. What do I need

I need to filter out only those rows in table UserData, that have any photo_* in jsonb Data column, i.e. Data field should contain key that starts with photo_

I have a hard time understanding how exactly your JSON values looks like, but I think you want something like this:

select ud.*
from userdata ud
where exists (select *
              from jsonb_object_keys(ud.data) as t(ky)
              where t.ky like 'photo%');


Limit hostnames for user I am running MySQL optimisation wizard, mysqltuner, which gives the suggestion:

  User 'root@%' does not specify hostname restrictions.

How is this achieved? I can't readily find an answer.

root, assuming it is the SUPER user, should be very restricted.

'root'@'my.domain.com'

'root'@'11.22.33.44'

In other situations, you might want

'joe'@'192.168.%'

'sal'@'%.our-domain.com'


Converting CHAR to DATETIME so I can use DATEADD() I have a question regarding using a DATEADD() function to a column that is identified as CHAR (6)

The time_stamp column holds values like 131329 as hhmmss.
Whenever I try to create a new column which will hold the value of the time_stamp column + 5 mins, I get an error.

  Msg 242, Level 16, State 3, Line 1
  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.`

What I am trying to do is create a new column that is 5 mins ahead of the time_stamp. I am working on a report right now that shows 'completed orders' but I need the report to hold the order as NOT COMPLETED for at least 5 mins before it shows on the report as Completed.

Well, first off, you should fix your table and store date/time data using the right kind of column, and not breaking it up for reasons unknown. Whose decision was it to store a time as a CHAR(6)? Can you think of a single good reason for that? Where do you store the date? Was that 1 PM today, last Tuesday, or October 2012 sometime? This really should be a single datetime column. Have the "designers" please read this and this.

But barring that (funny how every time you suggest a table change, they say they can't change the table):

DECLARE @x TABLE(y CHAR(6));

INSERT @x(y) VALUES('131329');

SELECT DATEADD(MINUTE,5,CONVERT(TIME(0),STUFF(STUFF(y,5,0,':'),3,0,':')))
  FROM @x;

Result:

13:18:29

If you find that query ugly and tedious, good! There's a good reason: You're storing your data wrong.

And this won't be the last problem you have with this design, either. What is stopping someone from inserting 967286 or foobar or <empty string> in your CHAR(6) column? If you need to work around that problem, you can use TRY_CONVERT() in 2012 and up (please always specify version with a version-specific tag), or a CASE expression in earlier versions.

DECLARE @x TABLE(y CHAR(6));

INSERT @x(y) VALUES('foobar');

-- 2012:
SELECT DATEADD(MINUTE,5,TRY_CONVERT(TIME(0), STUFF(STUFF(y,5,0,':'),3,0,':')))
  FROM @x;

-- older:
SELECT DATEADD(MINUTE,5,CONVERT(TIME(0),
CASE WHEN ISDATE(STUFF(STUFF(y,5,0,':'),3,0,':'))=1 THEN
    STUFF(STUFF(y,5,0,':'),3,0,':') END))
  FROM @x;


Unable to join on conrelid and confrelid columns in pg_constraint I'm trying to get a list of foreign key constraints in the following form:

pk_table | pk_col | fk_table | fk_col

without using pg_get_constraintdef (which would force me to resort to string manipulation).

Is there any reason why the following query doesn't work?

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.tablename,
  fk.tablename
FROM pg_catalog.pg_constraint x
INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND x.conrelid=(pk.tablename :: regclass :: oid)
INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid)
WHERE
  x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public'))
  AND x.contype='f'

Error:

ERROR:  relation "sql_implementation_info" does not exist

My hypothesis was that PG is getting confused due to the presence of multiple namespaces, so I tried restricting the result-set to my known schemas, i.e. b2b, public, but this still doesn't work.

The problem is that not all of "tables" presented in the pg_tables view have OID presented in the pg_class relation:

postgres=# select tablename::regclass from pg_catalog.pg_tables;
ERROR:  relation "sql_features" does not exist

You should to rewrite your query to more "low-level":

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.relname,
  fk.relname
FROM pg_catalog.pg_constraint x
INNER JOIN pg_catalog.pg_class pk ON x.conrelid!=0 AND x.conrelid=pk.oid
INNER JOIN pg_catalog.pg_class fk ON x.confrelid!=0 AND x.confrelid=fk.oid
WHERE
  x.contype='f';

test


Create relationship between two tables with composite primary key I've created a table [t1] with two fields and a composite primary key, show in below:

create table t1(
    id int,
    name nchar(10),
    primary key(id,name)
);

I want to create a second table [t2] that has a relationship with [t1], but I get an error with the below create table script:

create table t2(
    id int references t1(id),
    name nchar(10) references t1(name),
    primary key(id,name)
);

This the error message:

  Msg 1776, Level 16, State 0, Line 1
  There are no primary or candidate keys in the referenced table 't1' that match the referencing column list in the foreign key 'FK__t2__id__33D4B598'.

A multiple-column foreign key constraint must be declared at the table level, not on each column:

CREATE TABLE dbo.t1
(
    id int NOT NULL,
    name nchar(10) NOT NULL,

    CONSTRAINT PK_dbo_t1_id_name
    PRIMARY KEY CLUSTERED (id,name)
);

CREATE TABLE dbo.t2
(
    id int NOT NULL,
    name nchar(10) NOT NULL,

    CONSTRAINT PK_dbo_t2_id_name
    PRIMARY KEY CLUSTERED (id,name),

    CONSTRAINT FK_dbo_t2_t1_id_name
    FOREIGN KEY (id, name)
    REFERENCES dbo.t1 (id, name)
);


Partition elimination and date variables I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older than the retention date in order to catch any that are in the default.

The delete statement is not using partition elimination.

I select the date from a table into a date variable, then use that in a query:

declare v_retention_date date;

select retention_date
from schemaname.parametertable
into v_retention_date;

select count(*)
into v_row_count
from schemaname.tablename
where pdate < v_retention_date;

Other queries are doing partition elimination, those are passed in as text parameters to the procedure. Why might this query not do so?

Got an answer from Pivotal - I should have realised this, but parameterised queries are planned in ignorance of the parameter value, and partition elimination is part of the query plan, so we can't use variables for partition elimination.

I am re-tooling our partition elimination to use views that are dynamically constructed with an IN list of dates. This specific query is now just looking at the overflow partition as that's a lot simpler.


Dynamically change schema to accommodate custom fields! I am working on building a sort of a cms for a particular project. Nothing that will have a large volume of records in any table. Within 6 digits max.

I was thinking to create the default schema for all tables but then allow the editors to add custom fields to certain tables by adding fields to the schema. Obviously there would be the option to also drop fields.

I'm not an expert but neither a noob and I am sure I can write the proper codes to not get things mixed up and make a mess of it.

But still would this have any impact on the data integrity if changes are made on populated tables?

What are the hazards I could come across?

I did a presentation about the various solutions to support custom fields:

Extensible Data Modeling with MySQL.

The most straightforward option for greatest compatibility with SQL, including constraints and data types, is just to add columns as needed. But I would caution to not let users do this, instead let them submit a request and the DBA can perform the operation. A good tool to reduce impact is pt-online-schema-change.

Other solutions include:

EAV (but before you try it please read the article Bad CaRMa, and my blog post EAV FAIL)
Class table inheritance
Serialized LOB with Inverted Indexes
NoSQL document-store solutions


Alternative to using MIN() in a subquery? I have the following foo table:

+----+--------+-------+
| id |  name  |  qty  |
+----+--------+-------+
| 1  |  John  |   3   |
| 2  |  John  |   1   |
| 3  |  Mary  |   5   |
| 4  |  Mary  |   2   |
| 5  |  Gary  |   3   |
| 6  |  Gary  |   4   |
| 7  |  Gary  |   5   |
+----+--------+-------+

I would like to select only id and name of minimal Qty grouped by name, with this result:

+----+--------+
| id |  name  |
+----+--------+
| 2  |  John  |
| 4  |  Mary  |
| 5  |  Gary  |
+----+--------+

The only way I could do this was by means of the following operation:

SELECT id, name FROM (SELECT id, name, MIN(qty) FROM foo GROUP BY name) AS a;

Is there a more "beautiful" (or less redundant) way to do that?

This cannot be done without a subselect as you state.
There are two steps involved.
1.  For each name, find the record with the MIN(qty).
2.  Return the ID for that record.

There are other approaches but the two steps remain.

It appears the query you posted has some missing info. This should be the whole thing.

    SELECT id, name
FROM (
    SELECT name, MIN(qty) minqty
    FROM foo fs
    GROUP BY name
) AS a
    JOIN foo f
        ON f.name = fs.name
        AND f.qty = fs.minqty


Modify LISTAGG query for 10g below mentioned code for cursor works only in Oracle 11g, as LISTAGG() function introduced starting version 11g. I need to adapt this code for Oracle 10g. Any help would be appreciated.

SELECT DISTINCT (t3.field1), t3.field2,
       LISTAGG(t3.field3, ', ') WITHIN GROUP (ORDER BY t3.field3),
       SUM(t2.field1), SUM(t2.field2)
  FROM Table1 t1, Table2 t2, Table3 t3
 WHERE t1.id = t2.idoft1
   AND t2.id = t3.idoft2
GROUP BY t3.field1, t3.field2;

UPDATE:

Here is SQLFiddle version.

You need to pick one of the string aggregation techniques that works with your version and supports ordering. For example, I've adapted the "ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i" technique from the excellent ORACLE-BASE website:

WITH a as ( SELECT t3_field1,
                   t3_field2,
                   LTRIM(MAX(SYS_CONNECT_BY_PATH(field3,', '))
                       KEEP (DENSE_RANK LAST ORDER BY curr),', ') AS agg
            FROM   (SELECT field1 t3_field1,
                           field2 t3_field2,
                           field3,
                           ROW_NUMBER()
                             OVER (PARTITION BY field1, field2 ORDER BY field3)
                             AS curr,
                           ROW_NUMBER()
                             OVER (PARTITION BY field1, field2 ORDER BY field3) -1
                             AS prev
                    FROM   Table3)
            GROUP BY t3_field1, t3_field2
            CONNECT BY prev = PRIOR curr
                 AND t3_field1 = PRIOR t3_field1
                 AND t3_field2 = PRIOR t3_field2
            START WITH curr = 1 ),
     s as ( SELECT t3.field1 t3_field1,
                   t3.field2 t3_field2,
                   SUM(t2.field1) sum_t2_field1,
                   SUM(t2.field2) sum_t2_field2
            FROM Table1 t1, Table2 t2, Table3 t3
            WHERE t1.id = t2.idoft1 AND t2.id = t3.idoft2 AND t1.id = 3
            GROUP BY t3.field1, t3.field2 )
SELECT * FROM a JOIN s USING (t3_field1,t3_field2);

Results:

| T3_FIELD1 | T3_FIELD2 |                                             AGG | SUM_T2_FIELD1 | SUM_T2_FIELD2 |
|-----------|-----------|-------------------------------------------------|---------------|---------------|
|      asdf |      test | and_one_more_text, some_another_text, text_here |          1998 |           210 |
|     asdf2 |      test |                           text_in_another_group |           666 |            70 |

SQLFiddle here


Determining if indexes are redundant I have a table with the following structure:

Id (int identity PK)
FooId (int FK)
BarId (int FK)
QuxId (int FK)
Other fields

The following indexes have been defined:

PK (Id), clustered
Index1 (FooId)
Index2 (FooId, BarId)
Index3 (FooId, BarId, Id, QuxId)

Is it redundant to keep both Index1 and Index2?

Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

Would it make more sense to have just one index, (FooId, BarId, QuxId)? Would there be any benefit in including Id?

(as a sidenote, I'm a developer, not a DBA)

Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id).  Take a look at the following example:

use TestDB;
go

create table FooBar
(
    Id int identity(1, 1) not null
        constraint PK_FooBar_Id primary key clustered,
    FooId int not null,
    BarId int not null,
    QuxId int not null
);
go

create index Index1
on FooBar(FooId);
go

create index Index2
on FooBar(FooId, BarId);
go

create index Index3
on FooBar(FooId, BarId, QuxId);
go

insert into FooBar(FooId, BarId, QuxId)
values
    (1, 2, 3),
    (2, 3, 4),
    (4, 5, 6),
    (5, 6, 7),
    (6, 7, 8),
    (7, 8, 9);
go

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar
where FooId = 4;

By capturing the execution plan for the last query (the SELECT), you can see that it is an index seek on [FooBar].[Index3], and notice my CREATE INDEX doesn't include the Id column.

Martin brings up a good point in his comment (see below for quote):

  But specifying Id in a particular position in the index rather than just accepting the defaults can avoid a sort if the requirement is to ORDER BY that column order. Also allows a straight forward lookup on FooId, BarId, Id which an index on FooId, BarId, QuxId, Id wouldn't do (whether or not Id is included in the NCI key or leaf depends if the index is declared as unique or not)

Here is what he's talking about.  With a query like this (forced index table hint to show the Sort operation):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with (index(Index3))
where FooId = 4
order by Id;

This would generate the below plan:

But by changing the structure of the index, like so:

create index Index3
on FooBar(FooId, Id, BarId, QuxId)
with (drop_existing = on);
go

And re-executing the above query:

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar -- with (index(Index3))
where FooId = 4
order by Id;

You now don't have the expensive Sort operation:

Now take a look at what happens if your query does in fact look like the following (again, table hint there just to force Index3):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with(index(Index3))
where FooId = 4
and BarId = 5
order by Id;

You will again have that Sort operation.  Now if your index looked like the following:

create index Index3
on FooBar(FooId, BarId, Id, QuxId)
with(drop_existing = on);
go

By having Id as the third key column in the index structure, you will see the benefit of having it already ordered by Id when the Seek Predicates are on FooId and BarId.  Here is proof with that altered index:

To get a visual of that, let's look at the leaf page of Index3.  You can do this with the following:

dbcc ind('TestDB', 'FooBar', -1);
go
-- Index3 (index_id = 4) leaf page PID = 288
dbcc traceon(3604);
go
dbcc page('TestDB', 1, 288, 3);
go

You will get similar output like the following:


Postgres how to reference FK to primary composite key? Having a table definition like:
CREATE TABLE foo
(
    id1 Integer NOT NULL,
    id2 Integer NOT NULL
    PRIMARY KEY (id1, id2)
);

I found it impossible for linking a foreign key from another table to refer to each of the composite primary key above separately.
I get there is no unique constraint matching given keys for referenced table.
So I tried up with new table definition like:
CREATE TABLE foo
(
id1 Integer NOT NULL,
id2 Integer NOT NULL
UNIQUE (id1, id2),
PRIMARY KEY (id1, id2)
};

it didn't go well, so I ended up with- although not sure it is ok as I already have a primary composite key:
CREATE TABLE foo
(
id1 Integer NOT NULL,
id2 Integer NOT NULL
UNIQUE (id1),
UNIQUE (id2),
PRIMARY KEY (id1, id2)
};

What am I doing wrong? I want uniqueness in terms of composite primary key not on the seperated fields.

A foreign key constraint can only reference a unique or primary key constraint.
So if your primary key is defined on (id1, id2) and neither id1 nor id2 are unique, there is no way that you can reference the individual columns in a foreign key.
If, on the other hand, id1 and id2 are unique, then one of them should be the primary key, not the combination of both.
It is not clear what you want to achieve, but I'd suspect that the problem goes away when you normalize the tables.


MySql Workbench csv data import error I' trying to import a csv file in MySql WorkBench but get an error:

  Can't analyze file. Please, try to change encoding type. If that doesn't help, maybe the file is not: csv, or the file is empty.

I've tried to import the csv file create in Open Office Calac, Notepad++ or even just plain MS Notepad but no success. Of course I've set the encoding to UTF-8 in all of the programs.

Here is the the csv file content:

ę,1
ó,2
ą,3

csv files are tricky.

You should have a header in your csv file and also mark strings with apostrophes

But for your csv you must select latin2 as encoding see image

You must choose the right encoding, because this determines how bits and bytes are interpreted.

so if you don't want ansi you have encode it properly

See here for notepad++


Real Time SQL Server Analysis Services Cube Processing Report I would like to know if there is a way to generate a report to determine which SQL Server Analysis Services cubes are processing using SQL Server Reporting Services? I would like to be able to run this report without affecting the cube process, in real-time. The end product of this process is to create a way to let end users know the a report cannot be created because cube is processing.

I guess you are worried about the downtime due to cube processing.
As detailed in the post below, the downtime, due to processing, is very minimal .

http://www.jamesserra.com/archive/2011/06/can-you-query-a-ssas-cube-while-it-is-processing/

In the comments section, there is an approach mentioned by which you can reduce this downtime (with few caveats) by using one server to do the processing and then sync the DBs.

If you still want to do the report, you can follow the approach suggested here:

https://stackoverflow.com/questions/11899250/how-to-determine-which-ssas-cube-is-processing-now


Permission Error when trying to import SQL On an attempt to import an SQL into a Database I get this error.

* Error 0xc002f210: Preparation SQL Task 1: Executing the query "IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name..." failed with the following error: "CREATE TABLE permission denied in database 'WebsiteDatabase'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 (SQL Server Import and Export Wizard)

That error is fairly straightforward, the Import Wizard is checking if the destination table exists and trying to create it because it is missing, but your login doesn't have sufficient permissions on the database or server to create a table.

I'm assuming you're not a DBA based on the question, so you're probably best placed to have your DBA look at either creating the table for you, running the import for you or granting you the required permissions.


Poll table with multiple question types I'm designing a database for a poll system.

Questions can be one of two types: a numerical rating (1-5) or a voice record.

My idea is to have questions with a type field to specify if it's a numeric rating or an audio question.  I'd have two fields, response to store the numeric answers and audio_id to link to another table that can store the audio files.

However, in this design, there will always be a null field in each record. Is this is a good approach to solve the problem?

There is nothing inherently wrong with NULL values present in every row, especially if that solves your problem.

If you are using SQL Server, and are expecting the majority of rows to contain a numerical selection, with only a very small number of audio answers, you might decide to make the AudioID column "sparse".  See the Microsoft Docs for details about how to use sparse columns.

Definitive answers to database design questions like this are highly dependent on a variety of factors, none of which you've detailed in your question.  For instance:

How many rows are you expecting to insert, update, delete on daily, weekly, and monthly basis?
How wide will each row be?  If each row is hundreds of text columns wide that will necessitate a different answer than if each row is three integer columns wide.
Are you planning to query the data directly, or will you aggregate it from this table into a data warehouse?

If you can, please add the answers to these questions using the edit link directly beneath your question.


Row Compression in AlwaysOn Availability Groups I want to implement row compression on some databases that are in an availability group. Are row and page compression supported in availability group databases?
 Does it affect the replication, failover, configuration, or structure in any way? Does the role and mode of the node make a difference?

Compression is not listed as a restriction in the documentation at Prerequisites, Restrictions, and Recommendations for Always On availability groups, so that is the official reference that indicates it is supported with availability groups.  There are no special considerations for configuration, and it does not matter whether synchronous or asynchronous replication is used.

Row and page level compression will have no direct impact on availability groups as far as functionality is concerned, but there will be a small increase in CPU utilization on the primary server to compress the data when written, and uncompress when read, so it's possible that extra resource usage could impact availability group replication as it also requires CPU.  In general, though, if your system has adequate CPU this will be of no concern.  Additionally, if the secondary is used for reading, additional CPU will be used to uncompress the data.  Again, the CPU required to do this is quite minimal and will only be of concern if there are other factors causing the CPU utilization to be excessively high.

The replication process is simply copying transactions from the transaction log to the secondary servers (to oversimplify it a bit), so it really doesn't know or care about what data is actually in that transaction.  When the log record gets to the secondary, it is placed in the transaction log there and committed to the database just as if the transaction had originated locally.

If the replication process had to understand the contents of every transaction that is replicated, whether the data is compressed, encrypted, or whatever, it would be far slower.


How can I clear output of a query in pgAdmin III? I wonder whether I can clear the output of a query in pgAdmin III (aside from running another query).

I found this to be an interesting "lapse", if not bug, in the (normally excellent) pgAdmin3 software.

I double checked and it is is not possible to do what you want on Windows. Update, nor is it possible on Linux. Maybe a feature request? Though, at least the behaviour is consistent: ;-)

With respect to a feature request - it appears that the list is indeed the recommended method of submission. If you provide a link here, I can update with either the Linux or Windows behaviour - whichever you may not have.


Designing a reservation system schema I am not sure if this has been asked before, however I am tasked with designing a new reservation system for my organization

We have an older system which simply stored the booking in a table, and it worked pretty well, but it becomes quite slow when I try to determine what time slots are free (It has to get the open/close time range, and calculate whats free by taking away whats booked). Ideally, I would like a better solution for this, and I am hoping that somebody has solved the problem before.

The booking system itself will display only one day at a time, listing whats booked & whats available for that specific day.

As Joel says, you would be better off storing bookings rather than availability.  This will make any future changes to operating hours or booking conditions easier (slot length, minimum/maximum booking duration etc).  Also, you won't have to pre-populate an availability table out to some arbitrary future date.


Using sys.dm_os_performance_counters and DIFF to display logins per second PROBLEM

Running my script below i can see the number of logins as an accumulated number to my instance:

SELECT cntr_value AS [LoginsPerSec]
    FROM sys.dm_os_performance_counters
    WHERE
    object_name = 'SQLServer:General Statistics'
    AND counter_name = 'Logins/sec'

Running it again 1 second later returns an increased value.

The difference between the two appears to indicate the increased number of logins within that period of time (second) between the two executions.

QUESTION1

How do I execute this twice within the same script to retrun the DIFF between the two values ?

Simply need the DIFF value only as the result therefore allowing me to plot the value on a live chart.

Currently doing this for active connections using:

SELECT count(dbid) as NoOfConnections
FROM sys.sysprocesses
WHERE dbid = 5
GROUP BY dbid

QUESTION2

Can you recommend any other performance counters visable via sys.dm_os_performance_counters that would allow me to monitor where my CPU resource is being used by SQL server ?

Thanks for any help

Scott

Sorry, think this should work.

DECLARE @CurrentLoginUser int
DECLARE @CurrentLoginUser1 int

SET @CurrentLoginUser = (SELECT cntr_value AS [LoginsPerSec] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'Logins/sec')
WAITFOR DELAY '000:00:01'
SET @CurrentLoginUser1 = (SELECT cntr_value AS [LoginsPerSec] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'Logins/sec')

SELECT @CurrentLoginUser1 - @CurrentLoginUser


Use if else sql statement with two selects Currently I have the following SQL query
DECLARE @UgpEntry VARCHAR(50)

IF (@UgpEntry = -1)
SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     ,
           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [price_1]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='00004'

ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1) as [price_1],

           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [preciocaja]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='00004'

What I'm trying to do:

If this column t1.UgpEntry from OITM table (already exists and have data) is equal to -1 then
run the first query.

Else run the second query.

What am I doing wrong?
example: this record have UgpEntry = -1

this record have  UgpEntry = 5 and have another columns

I just added  SELECT @UgpEntry = UgpEntry FROM OITM t1 WHERE t1.ItemCode='' and it works
 DECLARE @UgpEntry VARCHAR(50)

SELECT @UgpEntry = UgpEntry FROM OITM t1 WHERE t1.ItemCode='v6p'

IF (@UgpEntry = -1)

SELECT
t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     ,
           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [price_1]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='v6p' and t1.UgpEntry = -1

ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1) as [price_1],

           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [preciocaja]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='v6p' and t1.UgpEntry > -1


Log to disk all queries that waited for a lock over a period of time I manage a mariadb 10.4.17 server, and I'm having the issues with a table, it seems randomly I will get lots of queries "waiting for metadata lock" causing my entire database to be useless.
I usually have to restart my server to get the database running again.
I'm looking for methods that can consistently log to disk all the queries who can use problems, without affecting performance too much.
I like something like this

watch -n 0.5 'mysqladmin -u root -ppassword "processlist"' > log.txt

But I don't know how to order by state. Anyway I'm open to any ideas. Looking for something I can look at to see what happened in the past because when the issue happens I just want to restart the db to get back online asap and don't have time to dig the root of the issue

You can do this by setting long_query_time as follows:
If you want it done permanently set it in mysql.cnf in the [server] section.
long_query_time=123.123 (down to millisecond precision)
From here - you can also set it dynamically.
Issue the command (from the mysql CLI (Command Line Interface) client):
SHOW VARIABLES LIKE '%long%'

or similar and set it. You can find out and/or set the destination file by issuing;
SHOW VARIABLES LIKE '%slow%'

Check out the db<>fiddle here!


Understanding the use of Sql command parameters I am using MySql database and I have a stored procedure to insert data, like this-

CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`(
    IN id varchar(10),
    IN pwd varchar(10),
    IN fname varchar(45),
    IN lname varchar(45),
    IN email varchar(45),
    IN phone varchar(13),
    IN address varchar(145),
    IN r1 varchar(45),
    IN r2 varchar(45),
    IN r3 varchar(45),
    IN r4 varchar(45),
    IN r5 varchar(45),
    IN r6 varchar(45),
    IN r7 varchar(45),
    IN r8 varchar(45)
)
BEGIN
    insert into users
    values(id,pwd,fname,lname,email,phone,address,'yes',r1,r2,r3,r4,r5,r6,r7,r8);
END

In my Asp.net website I have some code like this which calls the stored procedure-

using (cmd = new MySqlCommand("CALL vtsdb.adduser('" + txt_userId.Text + "','" + txt_pwd.Text + "','" + txt_firstName.Text + "','" + txt_lastName.Text + "','" + txt_emailId.Text + "','" + txt_phoneNo.Text + "','" + txt_address.Text + "','" + txt_f1.Text + "','" + txt_f2.Text + "','" + txt_f3.Text + "','" + txt_f4.Text + "','" + txt_f5.Text + "','" + txt_f6.Text + "','" + txt_f7.Text + "','" + txt_f8.Text + "')", con))
            {
                cmd.ExecuteNonQuery();
                con.Close();
            }

What I would like to know-

Do I need to use sql parameters in the above code? I dont see my stored procedure vulnerable to a Sql injection attack. Please guide me if I am wrong.

Also what is the use of mentioning CommandType.StoredProcedure in my c# code while my code still executes well?

Your stored procedure, itself, in a vacuum, as written now, does not appear vulnerable to SQL injection... but that is not the only consideration, and is not the final answer.

It's always possible that it could be updated later and become vulnerable, but there's yet something else to consider.

For a much bigger potential problem, take one step back and look at your calling convention.

If someone could manage to pass the following string value to your application as txt_f8.Text... it would be obediently concatenated into your MySqlCommand()...

stuff'); GRANT ALL PRIVILEGES ON *.* TO 'hacker'@'%' IDENTIFIED BY 'pwn3d'; SELECT ('1

...then what would be executed by your server?

Possibly, nothing.  Possibly, way too much.

This randomly-googled tutorial reinforces the point of not crafting queries with string concatenation, and using SqlParameter instead:

  This situation invites a hacker to replace that string with something
  malicious. In the worst case, you could give full control of your
  computer away.

  Instead of dynamically building a string, as shown in the bad example
  above, use parameters. Anything placed into a parameter will be
  treated as field data, not part of the SQL statement, which makes your
  application much more secure.

Certainly not a good trade-off for having to do less typing.

I'm a MySQL DBA and not a .NET person, but from what I can tell from a quick search it appears that declaring CommandType.StoredProcedure allows you to take better advantage of the capabilities of stored procedures, by making some of the parameters INOUT or OUT, and not just IN.  This might be useful, for example, if you wanted one of the parameters to be the ID of the newly-inserted user, which the SP could return to your code.

If you don't need return values, you don't have to do it that way... but feedback/confirmation isn't usually a bad thing.


Is it safe to let application administrators look at v$session, v$process, and v$sql? In an Oracle database shared among multiple application, is it 'safe' (from a security and system integrity perspective) to allow application administrators access to v$session, v$process, and v$sql? Or perhaps an overlying view limited to the application schema?

Thanks.

"Safe" is relative and involves a tradeoff between access and privileges and application and data security.

The factors that you would night want to consider are:

have the app admins had this access before?
Are the app admins employees or contractors?
what do they say they need it for and are there other ways to accomplish this?
do you have a development stack where they could have access?
who "owns" the database or applications?
Does each admin take care of one app and there is a
higher corporate owner? If this is the case a brief to the corporate owner should be your starting point.

My opinion is that unless there are performance or other application defects that are only seen on the production stack then access should not be granted on production. A view of the v$ views you mention on development should be fine.

Security procedures vary wildly from company to company and factors that are external to Oracle security are more often than not the deciding factors.

In short, only give access when you are satisfied that there is a real documented need that is supported by management.

In answer to the OP's question of what can you do with v$session I didn't know this till I looked for it but here is an example of what you can do with v$session.
That's the thing about security, it's not what you can imagine people can do with information that is dangerous.  It's what you don't know about.  To a point, less access to interesting system information is more security.


Microsoft SQL Server 2016 Service Pack 1 (SP1) support for TDE (transparent data encryption) Does Microsoft SQL Server 2016 Service Pack 1 add support for TDE (Transparent Data Encryption) to Standard Edition as well?

As per What's new in Database Engine - SQL Server 2016 and A Big Deal : SQL Server 2016 Service Pack 1 it looks like many of the Enterprise Grade features were added to SQL Server 2016 standard edition as well.

However, there's no clear answer about TDE, I know TDE was always a Enterprise feature, but just wanted to be sure before proposing SQL server 2016 with SP1.

Can anyone please refer to a written/documented proof whether SP1 with Standard Edition will have TDE or not?

Many thanks!

The documentation Editions and supported features of SQL Server 2016 was updated to cover what is, and what is not, added in SQL Server 2016 Service Pack 1.
Transparent Data Encryption is still an Enterprise-only feature.

You can find more details on the official SQL Server Release Services blog. The matrix there shows all new additions in SP1, to enable a common programming surface. Reproduced below in case the link stops working.

The following table compares the list of features which were only available in Enterprise edition which are now enabled in Standard, Web, Express, and LocalDB editions with SQL Server 2016 SP1. This consistent programmatically surface area allows developers and ISVs to develop and build applications leveraging the following features which can be deployed against any edition of SQL Server installed in the customer environment. The scale and high availability limits do not change, and remain as–is for lower editions


Randomly sort all non-ordered rows Is there a way to get PostgreSQL to randomly order query results that haven't been ordered by an ORDER BY? I think this would be a useful way to seek out bugs caused by an implicit reliance on the order of DB results.

Well, if there is no ORDER BY clause, the results is a non-ordered set, so the order that the rows are returned should be considered 'random' anyway.
However, PostgreSQL will typically fetch the rows in the most efficient manner, which may be the order they were inserted.
You can 'work around' this by performing a mock update, which will move the updated rows to the 'end' of the line. You can repeat several dissimilar updates using modulo, random or any other form that will filter a 'seemingly random' subset.
Something along the lines of:

DROP TABLE IF EXISTS Foo;

CREATE TABLE Foo (Col1 INT PRIMARY KEY);

INSERT INTO Foo (Col1)
SELECT * FROM generate_series(1, 20);

SELECT * FROM Foo;

UPDATE  Foo
SET     Col1 = Col1
WHERE   Col1%3 = 0;

UPDATE  Foo
SET     Col1 = Col1
WHERE   Col1%5 = 0;

SELECT * FROM Foo;

Do this in development environment only, not in production of course :-)

HTH


Password mismatch while logging to sql server Alright, I have a classic asp application and I have a connection string to try to connect to db.

MY connection string looks as follows:

 Provider=SQLOLEDB;Data Source=MYPC\MSSQLSERVER;Initial
 Catalog=mydb;database=mydb;User Id=me;Password=123

Now when I'm accessing db though front-en I get this error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'me'.

I looked in the sql profiler and I got this:

 Login failed for user 'me'.  Reason: Password did not match that
 for the login provided. [CLIENT: <named pipe>]
 Error: 18456, State:8.

What I've tried:

*checked 100 times that my password is actually correct.
*Tried this: alter login me with check_policy off (Do not even know why I did this)
*Enable ALL possible permissions for this account in SSMS.
* I've tried this connection string: Provider=SQLOLEDB;Data Source=MYPC\MSSQLSERVER;Initial Catalog=mydb;database=mydb; Integrated Security = SSPI

And I got this error:

Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database mydb requested by the login. The login failed.

The only times I have ever seen the State 8 error for 18456 (Password Mismatch) was in fact a password mismatch. Try a new password. Type it in notepad, copy and paste it to SQL and then do the same into your app config. Make sure nothing is happening to that password when it is being handed off to SQL - no trimming, extra spaces, etc.

But then the second error you got seems to indicate you may have gotten past that bad password error and are now unable to access your database.

That can be one of a few things:

1.) Verify the database specified really does exist.
2.) Verify that the login you are using has been granted appropriate permissions into that database (If the permissions are at the SQL Server system administrator level then this shouldn't be a necessary step)

That's where I'd start anyway.


What are best practices for safely permanently deleting a database? We have an "organic" environment, meaning people piled code on code for ten years with minimal oversight or documentation.  The server I use has several databases which I believe are no longer being used; I'd love to delete them and leave just the three I actually use.

At the reckless extreme, I could disable these databases and wait for someone to scream; at the other I could leave them running forever "just in case".  What steps have you found valuable in identifying whether a server is being used, and how?

Also, what steps would you recommend to ensure that, as one moves forward in disabling systems, that they remain conveniently reversible for a period of time (e.g., rename objects rather than deleting them outright)?

Thanks!

You could try to set up a trace that only captures connections and to what database they connect to. I would leave this running for a bit and then make sure nothing is connecting to it.

One problem with that would be if you have some code opening up on the master db but calling another DB within the code. I'm not sure how bad the code is that is pointing to your DBs.

I'd also query all your jobs and make sure none are pointing to that DB

You could also use SQL audit if you have the right version of SQL (2008 R2 enterprise).

You could also use logon triggers to update a table when someone logged on to that DB. This would show you if anything was connecting to that DB.


Is it safe to use mysqldump to backup and restore a master-master cluster Not sure if the specifics matter but just to get it out of the way I am talking about a Galera cluster on XtraDB (MariaDB).

I have a master-master setup and am interested in backing up and restoring the database if necessary.  On a single instance of MySQL I do the run of the mill

 mysqldump -u USER -p DB_NAME > DB-DATE.sql

 -and to restore-

 mysql -u USER -p < /path/to/dump.sql

I am not sure how these commands will fare in a clustered environment.  I'm going to divide this into two parts with my comments in each:

Backing up

The only issue I can think of as far as backing up goes is during the backup process some data could be written to the cluster, a query happens, my backup runs, then another related query happens leaving my data in an inconsistent state.  Let's say I have two tables users and counts.  When a user submits a form a PHP script updates the users favorite color in users as well as increments a field in counts called times_color_changed.  This simple script may look like:

<?
    //Yes, these are vulnerable to SQL injection but this is to demonstrate a point.
    $query1 = "UPDATE `users` SET `favorite_color` = ".$_POST['color']." WHERE id = $user_id";
    $query2 = "UPDATE `counts` SET `times_color_changed`  = `times_color_changed` + 1 WHERE id = $user_id";
    mysql_query($query1);
    mysql_query($query2);
?>

My worry is that while running the mysqldump in parallel the queries run as follows:

 QUERY_1 runs
 MySQL_DUMP
 Query_2 runs

 -or-

 Query1 from remote server commits on local server
 MySQL_DUMP
 Query2 from remote server commits on local server

Either of these can lead to an inconstant state.  I could always stop the SQL server but then I can't run MySQL dump.

There is an xtrabackup command but I'm not familiar with it. I've always done my dumps through mysqldump

Restoring

When restoring my dump how can I be sure that this replicates to the remote server?  Will the cluster think my dump is the "old" data and not replicate it?

I would strongly urge you to use Percona's XtraBackup. You're already using their storage engine - take the time to get to know this tool! Good reasons not to use mysqldump can be found here. If you really have to get text file SQL dumps of your data, check out mydumper, see here and here for why (disclaimer - haven't used).


Instance name is null - SQL Server I recently checked this in my SQL server instances:

SQL Server 2012 EE:

-- get current SQL Server name\instance name
  SELECT @@SERVERNAME
-- get current machine name and instance name
  SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

And the results show NULL value for instance name for some of the instances.

I did go through Instance name NULL

Sample output for one of my servers for SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

was

'Class024.dev.com', Null

Should I change the Instance name? Is it necessary or does it returns NULL if the instance is the default one?

It does return 'Null' if you've only got a default instance. I tried it in SQL 2005 and SQL 2008R2.
Otherwise, @@SERVERNAME should be the same as ServerProperty('MachineName') + '\' + ServerProperty('InstanceName')
Tested this also in SQL 2005 and SQL 2008R2.


Oracle Database 12c on Oracle Linux 7 - autostart, enabling connection as sysdba I've installed 12c on Oracle Linux 7 for my development work, as per document Oracle Database Installation Guide 12c Release 1 for Linux (E41491-10). My previous experience with administering Oracle Database is version 8 on Novell Netware and 10gR2 on Windows x64 at my prevoius workplace. I have not worked with Linux too much, save for some simple services on Ubuntu.

This time I choose not to create database on installation, so I had to run Oracle Database Configuration Assistant to do so, because I needed some options that default database did not have. I've done everything by the book, but I encountered problem one: ORACLE_HOME or ORACLE_BASE env vars were not set after creation. I had to edit .bash_profile to set these.

Database was created in default location, which is /home/oracle/app/oracle/oradata/<SID>. It was started and configured with listener. Upon completion of database creation, I noticed problem two: I can't connect to oracle instance using sqlplus / AS SYSDBA. I am presented with ORA-12162: TNS:net service name is incorrectly specified. As document Oracle Database Error Messages 12c Release 1 (E49325-06) states, I checked if TNSNAMES.ORA doesn't contain any errors in connect descriptor, but it doesn't. Full contents of this file are (KITET is my instance name):

KITET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORALINUX7VM)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = Kitet)
    )
  )

I connected only by specifying sqlplus <user>/<password>@<service_name>.

After rebooting my virtual machine, I noticed problem three: listener and oracle do not start automatically. I can start listener by using lsnrctl start, but can't start up the database (can't connect using / AS SYSDBA). I figured I could connect by specifying connection string with service_name, but I was greeted with ORA-12505: TNS:listener does not currently know of SID given in connect descriptor.

After looking here and there on the internet, I found out that by exporting ORACLE_SID, I could alleviate "can't startup" problem, so I did just that by editing (again) ~/.bash_profile.

What I still need done

I need listener and database startup automagically on boot, on Oracle Linux 7. Of course I am in posession of a document Oracle Linux Administrator's Guide for Release 7 (E54669) but there's no information on how to create startup scripts for Oracle Database and Listener. There's just info on starting and stopping EXISTING services. Of course I can't login to my VM box and startup services everytime I power it up.

Please assist.

Based on my own research, google results and ansible's answer, I was able to come up with complete solution for Oracle Linux 7. Everytime you read Kitet - this is my Oracle Instance SID.

To start or stop database, dbstart and dbshut scripts are used respectively. They are located in $ORACLE_HOME/bin. Both of them need a parameter, which is $ORACLE_HOME. If there's no $ORACLE_HOME environment variable, first there's a need to set it (and some more) by running

. /usr/local/bin/oraenv

(note the preceding period, and it's not $ORACLE_HOME/bin/oraenv). Oraenv script will ask for a SID, which is needed to set correct $ORACLE_BASE. With this, using simply

dbstart $ORACLE_HOME

or

dbshut $ORACLE_HOME

will start and stop the listener and database.

To start listener using lsnrctl or start SQL*Plus using sqlplus / as sysdba, running . oraenv beforehand is also required, unless user wants to set manually some environment variables. If our linux box has only one instance, there's a possibility of automating running of . oraenv by editing user's bash profile. Enter

nano ~/.bash_profile

and type following lines (there's probably already umask 022 entry in this file, as per Oracle Database Installation Guide 12c Release 1 for Linux E41491-10):

export ORACLE_SID=Kitet
export ORAENV_ASK=NO
. /usr/local/bin/oraenv
export NLS_LANG=POLISH_POLAND.AL32UTF8
unicode_start

Line one and three are self explanatory. Line two will make oraenv not ask for database SID and use exported variable. Line four is optional and will set your language preference for client connections (e.g. SQL*Plus or lsnrctl will speak in your native language, if available). Line five is needed for console to display accented characters, and is also optional.

Automating startup and shutdown of Listener and Database

1) Once the instance is created, edit the /etc/oratab file setting the restart flag for each instance to 'Y'. My line is as follows:

Kitet:/home/oracle/app/oracle/product/12.1.0/dbhome_1:Y

2) Next, create a file called /etc/init.d/dbora as the root user, containing the following:

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

# path to oracle home (needed only to check if dbstart exists)
ORA_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1
# this is the user who installed oracle
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle database and listener:
        # Remove "&" if you don't want startup as a background process.
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        # at this point we have $ORACLE_HOME env variable set
        su $ORA_OWNER -c "dbstart $ORACLE_HOME" &
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle database and listener:
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        su $ORA_OWNER -c "dbshut $ORACLE_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

3) Use the chmod command to set the privileges to 750:

chmod 750 /etc/init.d/dbora

4) Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:

chkconfig --add dbora

That's all. dbstart and dbshut should write to their respective logs at $ORACLE_HOME/startup.log and $ORACLE_HOME/shutdown.log. There also is $ORACLE_HOME/listener.log. There we can verify if and how instance and listener started and stopped. We could also use ps -ax | grep ora or ps -ax | grep lsnr to see if listener / oracle are running.


Table structure for validating data using category and subcategory fields I am creating a simple budget application, and the primary table will be a transaction table that contains things like the amount of the transaction, date, account, the category and subcategory (the type of transaction it was).

A spending category might be "Entertainment" or "Food" or "Household" and each would be further subdivided into subcategories.  The "Food" category might have "Work Lunch", "Grocery Store", "Fast Food", etc. subcategories, for example.

One way to do this is to have a table Categories:

    id    category     subcategory
----------------------------------
     1        Food      Work Lunch
     2        Food       Fast Food
     3        Food   Grocery Store
     4   Household        Mortgage
     5   Household         Repairs

and so on.  I then reference this id field in my table Transaction, using type as a foreign key:

id    txdate  amount   type   account   ...
-------------------------------------------
 1   6/25/15  15.25       2      cash   ... (a fast food expense)

The problem with this is that I'd like to be able to allow for null subcategories, and that doesn't seem to fit that well with this model.

Another way would be to just put the category and subcategory into the main Transaction table:

id    txdate  amount   category   subcategory   account   ...
--------------------------------------------------------------
 1   6/25/15   15.25       Food     Fast Food      cash                     ...

I have considered using CHECK CONSTRAINTs but this gets too bulky and messy too quickly, and does not allow me an easy way to access the allowable fields from my front-end code.  I will be validating data in the front end but I would like a robust way to ensure data validation on the actual database update as well, so I thought it would be convenient to store the values in a table (or more than one table), and then retrieve these allowable values in the code.

Any solution will work, but this is such a basic thing that I'm sure some best practices exist for this, and I'd like to query you all to find out what those would be.

Thank you for your time and expertise!

There are a few different strategies you could take, where on one end you pursue aggressive normalization to, on the other end, full denormalization.  The full denormalization would be equivalent to your second example where all relevant info simply ends up in the transaction table without references to other tables.

Full Normalization

So, to completely normalize, you would still want a Categories table, but you want to even eliminate the storage of redundant information in this table, so you would need a CategoryList table and a SubCategoryList table as

CategoryList

id    category
----------------
 1     Food
 2     Household
 etc...

and

SubCategoryList

id    subcategory
----------------
 1     Work Lunch
 2     Fast Food
 3     Grocery Store
 4     Mortgage
 5     Repairs
 etc...

You could then construct your category table from these two tables  as

Categories

id    category_id     subcategory_id
----------------------------------
 1        1               1
 2        1               2
 3        1               3
 4        2               4
 5        2               5

Treatment of NULL subcategories can easily be handled by either 1) simply placing a NULL entry for the subcategory_id column in the appropriate row of the Categories table, or 2) adding a subcategory entry id, subcategory where the subcategory field is NULL.

Last but not least you would add a foreigh key reference from your Transactions table to the appropriate id in the Categories table.

Does it really need to be so normalized?

Well, in my opinion, no it doesn't.  I've heard a quote, though I can't remember who spoke it, but it basically goes "Normalize until it hurts, denormailize until it works."  Especially in the case where you don't have a lot of categories, the fully normalized design may be a little bit of overkill.

What might simply make more sense would be to keep the above mentioned CategoryList and SubCategoryList tables to enumerate your types, but skip making the separate Categories table, and then simply have your Transactions table referencing the CategoryList and SubCategoryList tables as

id    txdate  amount   category   subcategory   account   ...
--------------------------------------------------------------
 1   6/25/15   15.25      1             2        cash                     ...

This way, you save on storage, and you can easily update/modify any category or subcategory entry in the list without needing to modify your entire Transactions table.  Further, you can simply permit the subcategory column of the Transactions table to permit NULL entries, if need be.

Hope this helps!


Replace " " character derived column SSIS So I am getting CSV Files and for some reason they are coming as "123-45-9873".

Now I trim the "-" out but the column is an SSN column so I am left with "12345987 taking the 3 out and putting the " character on the table.

What replace function can I use to replace the very character?

For reference, this is what I am doing

(DT_STR,9,1252)REPLACE(SSN,"-","")

and getting "12345678

Looks like the quote (") character is considered a part of the input string.

There is two options:
1. correct you flat file source to define quotation characters for this field, so it will remove " values from the beginning and end of the string.
2. Use another replace to get rid of quotes.REPLACE(REPLACE(SSN,"\"",""),"-","")


Setting shared_buffers over 320 MB prevents Postgres from restarting I'm running Postgres 8.1 on a CentOS 5 system with 4 GB of RAM. Whenever I set shared_buffers above 320 MB, it won't run.

I've checked SHMMAX and it's at 4294967295 which is the max allowed for a 32-bit/4 GB system. Below are some of the other memory related settings:

max_connections = 225
shared_buffers = 327680
work_mem = 524288
max_fsm_pages = 500000
max_fsm_relations = 10000

Any suggestions on what could improve this?

Just a misunderstanding I guess. The unit for the given number is blocks (or buffers), not 1 kb like you assume.
Quoting the manual for shared_buffers:

  Each buffer is 8192 bytes, unless a different value of BLCKSZ was
  chosen when building the server.

Bold emphasis mine.

SELECT pg_size_pretty(327680 *  8192::numeric);

pg_size_pretty
--------------
2560 MB

Not 320 MB. In modern Postgres the syntax with appended unit is preferred: 320MB, but I don't think that's implemented in the ancient Postgres 8.1, yet. For 320 MB set the GUC to:

shared_buffers = 40960

Have you considered installing a modern version of Postgres? (Like has been hinted in the comments ever so gently). Postgres 8.1 was released 10 years ago.


Are resource_semaphore_query_compile waits causing 100% CPU on server? Of late, the cpu usage on the db server is consistently at 100%. Even at 2 in the night when there is almost no traffic on the website and the load is negligible.

While trying to find the root cause, I see a lot of waiting tasks with wait type resource_semaphore_query_compile? All of these have started appearing lately in sys.dm_os_waiting_tasks

0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E9E28  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E8F28  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E94C8  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x008DC988  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x008DD798  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E9C48  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x594364D8  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E8C58  NULL    NULL    resourceWait

Why is this happening all of a sudden when nothing has changed? Also, the server 'self healed' itself yesterday, but the problem is back today? How to debug and fix this

Have you checked active tasks too, in addition to waiting ones? Check sys.dm_exec_requests for offenders with high cpu_time values. Look in sys.dm_exec_query_stats for queries with high total_worker_time.

An explanation for what is happening could be that a plan changed to result in a very CPU intensive execution plan with a large memory requirement (eg. hash joins, sorts). Parallel table scans and joins on non-aligned partitions come to mind... Few queries execute but they consume high CPU (the query exec and query stats views will reveal this). The queries require a large memory grant and cause other instances of the same query to block on the resource grant. A look in sys.dm_exec_query_resource_semaphores will reveal if this is true. Solution would be to address the plan stability, perhaps deploy plan guides.


it’s safe to run mysqltuner on production server Can i run mysqltuner directly on a production server ? It’s will slowdown my server or install some packages ? My users will be affected ?

Most monitoring services have small enough impact that they can (and should) be run on production servers.

mysqltuner -- once in a while.  (Tuning is not something that needs repeating very often.)
SHOW GLOBAL STATUS -- periodically.  (Some services use this to build graphs.)
Have the slowlog turned on (continually) even with a low, but non-zero, long_query_time.  (This is my favorite tool for getting useful clues about performance problems.)
performance_schema (early versions had a noticeable impact)
But not the general log -- This is likely to fill up the disk too fast.  When necessary, use it only briefly.

Here's what I request:  http://mysql.rjweb.org/doc.php/mysql_analysis  It includes

one-time SHOW VARIABLES and SHOW GLOBAL STATUS, then run that against about 200 checks.
one-time slowlog digest (after it has been running at least a day) -- followed by an analysis of the "worst" queries.  This often leads to improving the indexes and/or re-formulating some queries.  It may even lead to architectural changes to get away from problematical design patterns.


Trigger on PostGIS line layer occurs twice Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, as all of the triggers occur twice.

I first realized this because the history table inserts lines for the 'INSERT' and UPDATE logic twice (not for DELETE, surprisingly). The notices print to the console twice for INSERT, UPDATE, and DELETE.

For example:

When creating a line in the base table, the history table creates two objects with the same attributes except for the hid (history table id).
When updating the attribute of an existing line, in the history table, three objects are created:

One with etat = 'MODIFICATION COURANTE' (resembles the existing feature in the base table, including the updated attribute)
Two with etat = 'MODIFICATION ARCHIVEE' -- one with the old attribute value and one with the new attribute value. At this point, I am expecting only one object containing the old attribute. The object containing the new attribute is out if place.

CREATE or REPLACE FUNCTION test.test_track_history_tracker() RETURNS trigger AS
$new_test_track_history_tracker$
BEGIN
  -- INSERT

  IF (TG_OP = 'INSERT') THEN
     INSERT INTO test."Conduite_test_history"
        (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'CREATION', NEW.geom);
     raise notice 'Insert happened';
     RETURN NEW;

  -- UPDATE
  ELSEIF (TG_OP = 'UPDATE') THEN
     UPDATE test."Conduite_test_history"
         SET deleted = current_timestamp, deleted_by = current_user, modified = TRUE, etat = 'MODIFICATION ARCHIVEE'
     WHERE deleted IS NULL and gid_org = OLD.gid;

     INSERT INTO test."Conduite_test_history"
         (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'MODIFICATION COURANTE', NEW.geom);
     raise notice 'Update happened';
     RETURN NEW;
  -- DELETE
  ELSEIF (TG_OP = 'DELETE') THEN
     UPDATE test."Conduite_test_history"
       SET deleted = current_timestamp,
           deleted_by = current_user,
           etat = 'SUPPRESSION'
     WHERE deleted is NULL and gid_org = OLD.gid;
     raise notice 'Delete happened';
     RETURN NULL;
  END IF;
END;
$new_test_track_history_tracker$
LANGUAGE plpgsql;

--ADD TRIGGER
DROP TRIGGER IF EXISTS trg_test_track_history_tracker ON test."Conduite_test";
CREATE TRIGGER trg_test_track_history_tracker
  AFTER INSERT OR UPDATE OR DELETE ON test."Conduite_test"
  FOR EACH ROW EXECUTE PROCEDURE test.test_track_history_tracker();

You are using an AFTER trigger - thus any action will have already taken place and the UPDATE will actually run twice: once the for the "original" statement and then again from within your trigger.

It seems to be, you actually want a BEFORE trigger. And there you just assign values to the NEW row, rather than UPDATEing the table itself.

So I think what you are looking for is something like this:

CREATE or REPLACE FUNCTION test.test_track_history_tracker()
  RETURNS trigger AS
$new_test_track_history_tracker$
BEGIN
  -- INSERT

  IF (TG_OP = 'INSERT') THEN
     INSERT INTO test."Conduite_test_history"
        (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'CREATION', NEW.geom);
     raise notice 'Insert happened';

     -- continue with the INSERT
     RETURN NEW;

  -- UPDATE
  ELSEIF (TG_OP = 'UPDATE') THEN
     -- this IF is equivalent to your WHERE statement
     IF new.deleted IS NULL THEN
       new.deleted := current_timestamp;
       new.deleted_by := current_user;
       new.modified := true;
       new.eta := 'MODIFICATION ARCHIVEE';
     END IF;

     INSERT INTO test."Conduite_test_history"
         (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'MODIFICATION COURANTE', NEW.geom);
     raise notice 'Update happened';

     RETURN NEW; -- continue with the UPDATE
  -- DELETE
  ELSEIF (TG_OP = 'DELETE') THEN

     -- this is correct: it will turn the DELETE into an UPDATE
     UPDATE test."Conduite_test_history"
        SET deleted = current_timestamp, deleted_by = current_user, etat = 'SUPPRESSION'
     WHERE deleted is NULL and gid_org = OLD.gid;
     raise notice 'Delete happened';

     RETURN NULL; -- this will prevent the actual DELETE in t
  END IF;
END;
$new_test_track_history_tracker$
LANGUAGE plpgsql;

And then create a BEFORE trigger using that trigger function:

--ADD TRIGGER
DROP TRIGGER IF EXISTS trg_test_track_history_tracker ON test."Conduite_test";
CREATE TRIGGER trg_test_track_history_tracker
  BEFORE
       INSERT OR UPDATE OR DELETE ON test."Conduite_test"
  FOR EACH ROW EXECUTE PROCEDURE test.test_track_history_tracker();


Performance of a big INSERT...SELECT with multiple JOIN I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production server, the query execute in 20min.

First question: is it slow or average considering the size of the base?

Second question (if slow): here the request, is there room for improvement (I think there is)? I'm considering using chunking but I'm not sure how.

INSERT INTO output
(id_people, id_product)

SELECT p.id_people, 146
FROM people p
INNER JOIN sell s
  ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46')
INNER JOIN group g
  ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c
  ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN company co
  ON (co.id_company = c.id_company)

WHERE g.type = 'OD'
AND g.start < '2017-08-09 13:29:12.46'
AND g.end > '2018-08-09 13:29:12.46'

ON DUPLICATE KEY UPDATE

Any hint will be very welcome :)

You may not be aware of this, but did you know that MySQL allocates a distinct join buffer for each additional JOIN clause ?
According to the MySQL Documentation for join_buffer_size:

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.
The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).
For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

This is also declared in Block Nested-Loop Join Algorithm

MySQL join buffering has these characteristics:
Join buffering can be used when the join is of type ALL or index (in
other words, when no possible keys can be used, and a full scan is
done, of either the data or index rows, respectively), or range. Use
of buffering is also applicable to outer joins, as described in
Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

A join buffer is never allocated for the first nonconstant table, even
if it would be of type ALL or index.

Only columns of interest to a join are stored in its join buffer, not
whole rows.

The join_buffer_size system variable determines the size of each join
buffer used to process a query.

One buffer is allocated for each join that can be buffered, so a given
query might be processed using multiple join buffers.

A join buffer is allocated prior to executing the join and freed after
the query is done.

Based on this, you must increase the join_buffer_size in your session just before running your INSERT ... SELECT. From the look of the join's ON clauses, I don't foresee indexes ever being selected by the query optimizer.
I cannot tell you how big to make the join_buffer_size. You will have to experiment with it and run the EXPLAIN plan to see if the SELECT will avoid writing to disk.
WARNING : According to the last paragraph of MySQL Documentation on Nested-Loop Join Algorithms:

The number of t3 scans decreases as the value of join_buffer_size increases, up to the point when join_buffer_size is large enough to hold all previous row combinations. At that point, no speed is gained by making it larger.

This why you should experiment with join_buffer_size.
HOW TO EXPERIMENT TO join_buffer_size
You can change the join_buffer_size one MB at a time. Then, run the SELECT and time it.
STEP 01 : Set an initial size of 0 and initial runtime of 48 hours (172800 seconds)
SET @jbsize = 0;
SET @prev_runtime = 172800;

STEP 02 : Increment 1MB
SET @jbsize = jbsize + 1;
SET SESSION join_buffer_size = @jbsize * 1048576;

STEP 03 : Time the SELECT
SET @t1 = UNIX_TIMESTAMP();
SELECT p.id_people, 146
FROM people p
INNER JOIN sell s
  ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46')
INNER JOIN group g
  ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c
  ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN company co
  ON (co.id_company = c.id_company)
WHERE g.type = 'OD'
AND g.start < '2017-08-09 13:29:12.46'
AND g.end > '2018-08-09 13:29:12.46'
SET @t2 = UNIX_TIMESTAMP();

STEP 04 : Compute Time
SET @curr_runtime = @t2 - @t1;
SET @diff_runtime= @prev_runtime - @curr_runtime;
SELECT
    sec_to_time(@prev_runtime)     Previous_Running_Time,
    sec_to_time(@curr_runtime)      Current_Running_Time,
    sec_to_time(@diff_runtime) Improvement_From_Last_Run
;

STEP 05 : Decision Time
If Improvement_From_Last_Run is big in terms of hours, minutes, and seconds and you feel making join_buffer_size bigger would improve the run time, loop back to STEP 02. Keep doing this until Improvement_From_Last_Run is 00:00:00.
GIVE IT A TRY !!!


What is the least amount of privileges (Administrator, db_owner, etc ) for a user to perform Backup and Restore in SQL Server? What is the least amount of privileges (Administrator, db_owner, etc) for a user to perform following in SQL Server ?

Backup
Restore

For backups the db_backupoperator fixed database role is the least amount of privileges needed.

https://msdn.microsoft.com/en-gb/library/ms187510.aspx

For Restores, If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE.

https://msdn.microsoft.com/en-gb/library/ms186858.aspx


should parent entity always have a primary key? I am have 3 entities, Person(fname, lname) none of them is primary, then User(email, password) and Receptionist(emp_id) bold is primary key. I have extended there two entities from Person, total participation and overllapping, so person must be either user or receptionist or both at the same time. Is it okay if I do not use primary key in Person

How can you identify each record if you don't have primary key. What if there is person having the same name and use it for future data. You must have primary key for table Person.


How can we select data from another table during an insert? I'm trying to insert data into a table using a few columns that are hard-coded or are static values and then the rest of the datat selected from another table. I've tried the following query:
insert into wtdocversion
values (( select id from wtdocmaster),'col2','col3');

But this generates an error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

You want to drop the values part of the query and do an INSERT\SELECT.

This is really basic SQL and you can read more about it in BOL.

INSERT INTO wtdocversion (col1, col2, col3)
SELECT
  id,
  'col2',
  'col3'
FROM wtdocmaster;


Schema binding seems to not be enforced for view doing temporal query I am using Microsoft SQL Server 2019.
There are two strange behaviors that I don't see documented (if it is, please point me to the Microsoft docs link), I suspect they're related.

The INFORMATION_SCHEMA.VIEW_COLUMN_USAGE system view does not show information about view queries that do temporal queries.

If I create a view which does a temporal query, schema binding is not enforced. I can alter the columns on the underlying table that the view's query uses. Normally, creating the view with the "WITH SCHEMABINDING" option would cause such attempts to alter the table to fail.

Try the sample code below to see that behavior. I added "!!" in the comments at the spots where things did not work out as I expected them to.
My main question: is this a bug or is this expected behavior?
--cleanup from past run
DROP VIEW IF EXISTS [vwTemporalTest_doingTemporalQuery]
DROP VIEW IF EXISTS [vwTemporalTest_noTemporalQuery];
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TemporalTest')
BEGIN
    ALTER TABLE [TemporalTest] SET (SYSTEM_VERSIONING = OFF);
END
DROP TABLE IF EXISTS [TemporalTest];
DROP TABLE IF EXISTS [TemporalTestHistory];
GO

--create the temporal table
CREATE TABLE [TemporalTest]
(
    [Id] [int] NOT NULL,
    [Name] nvarchar(500) NOT NULL,
    [Description] nvarchar(100) NULL,
    [period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([period_start], [period_end]),
    PRIMARY KEY CLUSTERED ([Id])
) ON [PRIMARY]
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON ))
GO

--insert some data
INSERT INTO [TemporalTest] ([Id],[Name]) VALUES
(1, 'Alice'),
(2, 'Bob');
--just do an update so some history row gets populated
UPDATE [TemporalTest] SET [Name] = 'Bobby', [Description] = 'blah' WHERE [Id] = 2;
GO

--create the views (first one won't do a temporal query, second one will do a temporal query)
-- and they'll reference different columns to showcase the issue (first one uses Name, second uses Description)
CREATE VIEW [vwTemporalTest_noTemporalQuery]
WITH SCHEMABINDING
    AS SELECT [Id], [Name] FROM [dbo].[TemporalTest];
GO
CREATE VIEW [vwTemporalTest_doingTemporalQuery]
WITH SCHEMABINDING
    AS SELECT [Id], [Description], [period_start], [period_end] FROM [dbo].[TemporalTest] FOR SYSTEM_TIME ALL;
GO

--test out the views
SELECT * FROM [vwTemporalTest_noTemporalQuery]; --returns 2 current rows
SELECT * FROM [vwTemporalTest_doingTemporalQuery]; --returns 3 rows (including 1 historical row)

--look at information about the views
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where View_Name = 'vwTemporalTest_noTemporalQuery' --returns 2 rows (one for each column used)
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where View_Name = 'vwTemporalTest_doingTemporalQuery' --!!returns no rows (I expected 4 rows at least since the view uses 4 columns)

/*
--now verify schema binding prevents the underlying table from being altered
ALTER TABLE [TemporalTest] ALTER COLUMN [Name] nvarchar(600) NOT NULL; --fails as expected - The object 'vwTemporalTest_noTemporalQuery' is dependent on column 'Name'.
ALTER TABLE [TemporalTest] ALTER COLUMN [Description] nvarchar(255) NULL; --!!unexpectedly succeeds. I expected it to fail because vwTemporalTest_doingTemporalQuery uses the Description column.
*/

I think one thing in this case must be considered: your view is not referencing the temporal table directly, it is using a feature from SQL Server to get data that is stored on a different table (the history table).
The graph from the doc How do I query temporal data shows how you get the data from a temporal table:

If the picture shows the real data flow, your query doesn't directly "touch" the history table. Therefore, your view with the history data is not exactly dependent on the history table.
Of course the following is not the appropriate way of manipulate temporal data, but I think it helps to exemplify my point:
CREATE VIEW [vwTemporalTest_doingTemporalQuery2]
WITH SCHEMABINDING
    AS SELECT [Id], [Description], [period_start], [period_end]
       FROM [dbo].[TemporalTestHistory];
GO

This view is directly referencing the TemporalTestHistory table and when you try to alter the history table as you did before, it throws the error you expected. Also, this view appears on the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.

Important remarks
Under the Important remarks of the Creating a system-versioned temporal table you can see that:

The history table must always be schema-aligned with the current or    temporal table, in terms of number of columns, column names, ordering
and data types.

And the Important remarks section of the Changing the schema of a system-versioned temporal table doc says:

Specified schema change is propagated to history table appropriately    (depending on type of change).

From those remarks it seems we're not supposed to do changes directly on the history table as a good practice.
About the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE not showing any info regarding that view I couldn't find any explanation. By the way, this is the conclusion I could get from tests and reading the docs I mentioned and I posted it as it might help to get to a more accurate answer (maybe it will be considered as a bug like you mentioned).


Object_name(object_id) returning incorrect results We are trying to exclude stored procedures which have name like '%backup%' or %WI%'..

Here is the total query i used..

;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' and ( object_name(object_id) NOT LIKE '%BACKUP%'
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%')
)
SELECT * FROM CTE

sample Output of above query:
  report_spBIHRRecruitmentReport_Phase2_backup

But strangely above query is returning results which still have names
like
1.backup
2.Wi

Then i tried to use collate to restrict results like results..

;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' collate SQL_Latin1_General_CP1_CI_AS
 and ( object_name(object_id) NOT LIKE '%BACKUP%'
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%' collate SQL_Latin1_General_CP1_CI_AS
))
SELECT * FROM CTE

Output still contains names like below:

report_spBIHRRecruitmentReport_Phase2_backup

Below is my database info:
Version:SQL Server 2012
Collation:SQL_Latin1_General_CP1_CI_AS
comptabilty level :90

ASK:
Can you please help me understand why i am still getting names like '%backup% or '%wi%'

Scripts to Reproduce:

create proc usp_test1_backup
as
begin
end

create proc usp_test2_wi
as
begin
end

drop proc usp_test1_backup
drop proc usp_test2_wi

Execution plan of my query if it helps you:

http://pastebin.com/aKBqkPzj

Please let me know if you need any further info

The answer is quite simple. You're using a load of ORs, which should be ANDs:

object_name(object_id) NOT LIKE '%BACKUP%'
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%'

In your case, object_name(object_id) not like '%backup%'  will still match OR object_name(object_id) NOT LIKE '%Wi%' and so will return the row etc etc.

Change all of the above conditions to AND and you'll be OK.


How to Get All Server Side Traces in SqlServer from SSMS, stop it from SSMS and delete the log files How to Get All Server Side Traces in SqlServer from SSMS(Active,Inactive), stop it from SSMS and delete the log files.
I need all traces saving into file as well as table.

I tried SELECT * FROM fn_trace_getinfo(DEFAULT). Some times few parameters are NULL. What does it mean ?

Trace properties with NULL values indicate options were not specified when the trace was created. Properties are listed in the fn_trace_getinfo documentation and can be included a query for easy reference:

SELECT traceid, trace.property, trace_properties.description, trace.value
FROM fn_trace_getinfo(DEFAULT) AS trace
JOIN (VALUES
     (1, 'Trace options. For more information, see @options in sp_trace_create (Transact-SQL)')
    ,(2, 'File name')
    ,(3, 'Max size')
    ,(4, 'Stop time')
    ,(5, 'Current trace status. 0 = stopped. 1 = running.')
    ) AS trace_properties(property, description) ON trace_properties.property = trace.property
ORDER BY trace.traceid, trace.property;

Rowset traces (Profiler or SMO) will not have values pertaining to server-side traces, such as file name, max size, etc. Below are example results of the above query including the default trace and a running Profiler trace.

+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+
| traceid | property |                                     description                                     |                                      value                                      |
+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+
|       1 |        1 | Trace options. For more information, see @options in sp_trace_create (Transact-SQL) | 2                                                                               |
|       1 |        2 | File name                                                                           | D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_175.trc |
|       1 |        3 | Max size                                                                            | 20                                                                              |
|       1 |        4 | Stop time                                                                           | NULL                                                                            |
|       1 |        5 | Current trace status. 0 = stopped. 1 = running.                                     | 1                                                                               |
|       2 |        1 | Trace options. For more information, see @options in sp_trace_create (Transact-SQL) | 1                                                                               |
|       2 |        2 | File name                                                                           | NULL                                                                            |
|       2 |        3 | Max size                                                                            | NULL                                                                            |
|       2 |        4 | Stop time                                                                           | NULL                                                                            |
|       2 |        5 | Current trace status. 0 = stopped. 1 = running.                                     | 1                                                                               |
+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+

The trace properties are also exposed as normalized columns in sys.traces. This DMV includes all legacy SQL Traces (but not Extended Event traces) regardless of whether its a server-side trace to a file or rowset trace returning data to a Profiler client. You may find it easier to query sys.traces instead. Example output from SELECT * FROM sys.traces.

+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+
| id | status |                                      path                                       | max_size | stop_time | max_files | is_rowset | is_rollover | is_shutdown | is_default | buffer_count | buffer_size | file_position | reader_spid |       start_time        |     last_event_time     | event_count | dropped_event_count |
+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+
|  1 |      1 | D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_175.trc | 20       | NULL      | 5         |         0 |           1 |           0 |          1 |            2 |        1024 | 1048576       | NULL        | 2020-05-20 07:44:55.487 | 2020-05-24 08:25:08.983 |        1088 | NULL                |
|  2 |      1 | NULL                                                                            | NULL     | NULL      | NULL      |         1 |           0 |           0 |          0 |         1088 |           1 | NULL          | 94          | 2020-05-24 08:18:00.503 | 2020-05-24 08:25:08.983 |          74 | 0                   |
+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+

EDIT:

A trace can be stopped and deleted in SSMS by executing sp_trace_setstatus from a query window, specifying the desired trace id and status (0 = stop, 2 = delete). For example, the rowset trace with traceid 2 above can be stopped and deleted with:

EXEC sp_trace_setstatus 2, 0; --stop trace id 2
EXEC sp_trace_setstatus 2, 2; --delete trace id 2


mysql max_binlog_size is not working I have a weird problem. I am currently using mysql and have defined 100M of max_binlog_size. However, I have hundreds of files created every day and each file is of no more then 2MB or 3MB each and sometime less.

I do not understand why is this so the file should be around 100M each?

Can someone have insights to it.

When binary logs do not reach the limit set by max_binlog_size, there are two(2) major reasons why

mysqld closes the currently open binlog and opens a new binlog

Running service mysql restart
Running service mysql top followed by service mysql start
mysqld crashes and mysqld_safe restarts it if the mysqld crash was not severe

Running FLUSH LOGS will do the following

Close and Reopen the Error Log
Close and Reopen the General Log
Close and Reopen the Slow Log
Close and Reopen Engine Logs
Close the Currently Open Binary Log and Open a New Binary Log

It says the following the MySQL Documentation under FLUSH LOGS

  With no log_type option, FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.

SUGGESTIONS

Look in your error log to see if mysqld crashes and restarts. The timestamp should match the binlog that was closed. The error log will also show the reason for the crash, or at least leave you a trail to attempt to troubleshoot
If your code or some cronjob is running FLUSH LOGS, do one of the following:

Replace it with FLUSH ENGINE LOGS;
Stop using FLUSH LOGS altogether
Live with it. Having 2M or 3M binary logs is not all that bad


What is a non-atomic yield in the context of SQL Server? There's a SQL Server performance counter for 'Non-atomic yield rate'.

The documentation simply states that this is

  Number of non-atomic yields per second.

I'm having trouble finding an explanation for what a non-atomic yield is.

What is a non-atomic yield in the context of SQL Server?

The following is what an Atomic block in SQL is:

  Every natively compiled stored procedure contains exactly one block
  of Transact-SQL statements. This is an ATOMIC block.

This is how Atomic blocks relate to transactions and error handling:

BEGIN ATOMIC

  If a transaction already exists on a session (because a batch executed
  a BEGIN TRANSACTION statement and the transaction remains active),
  then starting an atomic block will create a savepoint in the
  transaction. If the block exits without an exception, the savepoint
  that was created for the block commits, but the transaction will not
  commit until the transaction at the session level commits. If the
  block throws an exception, the effects of the block are rolled back
  but the transaction at the session level will proceed, unless the
  exception is transaction-dooming. For example a write conflict is
  transaction-dooming, but not a type casting error.

Atomic Blocks in Native Procedures


Calculate Value Dynamically I need to calculate a value by using it's previous value * ColumnX

Here is the formula:

DividendReinvested = "Previous DividendReinvested" * (1+ValuationPercChange/100)

Can I do this without a loop? CTE possible? Using SQL Server 2008 R2.

Here is sample data. I use RowNum as unique identifier to set current row from previous row.

I found this 'running totals' CTE example here (you should be able to adapt it to your needs). Note the MAXRECURSION
https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance

DECLARE @Tbl TABLE (RowNum INT,Columnx INT)

INSERT INTO @Tbl (RowNum,Columnx)
VALUES (1,100)

INSERT INTO @Tbl (RowNum,Columnx)
VALUES (2,200)

INSERT INTO @Tbl (RowNum,Columnx)
VALUES (3,300)
;
WITH x
AS (
    SELECT RowNum
        ,Columnx
        ,RunningTotal = Columnx
    FROM @tbl
    WHERE RowNum = 1

    UNION ALL

    SELECT y.RowNum
        ,y.Columnx
        ,x.RunningTotal + y.Columnx
    FROM x
    INNER JOIN @tbl AS y ON y.RowNum = x.RowNum + 1
    )
SELECT RowNum
    ,columnx
    ,RunningTotal
FROM x
ORDER BY rownum
OPTION (MAXRECURSION 10000);


Recommendation on changing Azure SQL Database tier weekly We have an Azure SQL Database of size 50GB in the S6 tier. On weekends, the usage is very minimal.

Is it okay to change the tier to S0 every weekend for cost savings, and change back to S6 on Monday?

Are there any other steps to perform prior to the tier change? Do you have any other suggestions?

Azure SQL Database is designed to scale up and down based on your workload. I cannot answer your question "is it ok to change the tier to S0 every weekend for cost savings and change back to s6 on Monday" with yes or no.

You also mentioned usage is very minimal. S0 gives you maximum 10 DTU. Do you know for your "very minimal" workload 10 DTU is enough or no. Is your application written to retry disconnected sessions?

From books online:

  Existing connections to the database in the original compute instance
  are dropped. Any new connections are established to the database in
  the new compute instance. For some combinations of service tier and
  compute size changes, database files are detached and reattached
  during the switch. Regardless, the switch can result in a brief
  service interruption when the database is unavailable generally for
  less than 30 seconds and often for only a few seconds. If there are
  long running transactions running when connections are dropped, the
  duration of this step may take longer in order to recover aborted
  transactions. Accelerated Database Recovery can reduce the impact from
  aborting long running transactions.

Few other resources for your third point "any other steps to perform prior tier change and suggestions please".

Scale single database resources in Azure SQL Database
Recovering from a possibly failed scale-up
Stop / Cancel ongoing Azure SQL DB scale operation
sys.dm_operation_status (Azure SQL Database)