Determine Index Compression Candidates Online By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression:

ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE;
SELECT Opt_Cmpr_PctSave, Opt_Cmpr_Count FROM Index_Stats;

The problem is that when OFFLINE is changed to ONLINE the Index_Stats view does not get populated.  Is there an online way to determine the benefit of compressing an index and/or the number of columns that will produce optimal compression?

Update:

http://jonathanlewis.wordpress.com/index-definitions/ indicates that if Distinct_Keys from DBA_Indexes is "a lot smaller" than num_rows then the index is a good candidate for compression.  This helps some, but isn't definitive and doesn't help determine the number of columns.  He does give some guidelines for that, but nothing that can be determined programatically without a bunch of dynamic SQL.

The optimal number of columns to compress depends on:

The number of entries that will fit in each block (this depends on the number of compressed columns as they are only stored once per block)
The average number of entries with same prefix

These factors can be estimated for the table

The aim is to maximize the size of the compressed prefix whilst minimizing the number of blocks needed to hold all rows with the same prefix.

Assuming that the data is uniform at least to a degree, and ignoring the small amount of overhead compression introduces, you could attempt to implement this approach like this:

helper functions:

create or replace function f_size( p_table_name in varchar,
                                   p_column_name in varchar)
                  return number as
  n number;
begin
  execute immediate
    'select avg(vsize('||p_column_name||'))+1 from '||p_table_name into n;
  return n;
end;
/

create or replace function f_count( p_table_name in varchar,
                                    p_column_names in varchar )
                  return integer as
  n integer;
begin
  execute immediate 'select count(*) '||
                    'from ( select '|| p_column_names ||
                           ' from '||p_table_name||' '||
                           'group by '||p_column_names||' )'
          into n;
  return n;
end;
/

test IOT:

create table t ( k1, k2, k3, k4, k5, val,
                 constraint pk_t primary key(k1, k2, k3, k4, k5))
       organization index as
select mod(k,10)||'_____',
       mod(k,20)||'_____',
       mod(k,30)||'_____',
       mod(k,50)||'_____',
       k||'_____',
       lpad(' ',100)
from (select level as k from dual connect by level<=1000);

query:

with utc as (select table_name, column_name, f_size(table_name, column_name) as column_size from user_tab_columns where table_name='T'),
     uic as (select table_name, column_name, column_position, column_size from user_ind_columns join utc using(table_name, column_name) where index_name='PK_T')
select z.*, (8192-prefix_size*prefixes_per_block)/remaining_size as rows_per_block
from( select z.*, greatest(1,8192/(prefix_size+rows_per_prefix*remaining_size)) as prefixes_per_block
      from( select z.*, total_count/distinct_count as rows_per_prefix
            from( select prefix_length, sum(column_size) as prefix_size, (select sum(column_size) from utc)-sum(column_size) as remaining_size, f_count(table_name, max(prefix_columns)) as distinct_count,
                         (select count(*) from t) as total_count
                  from( select table_name, connect_by_root column_position as prefix_length, column_size, substr(sys_connect_by_path(column_name, ','),2) as prefix_columns
                        from uic
                        connect by column_position=(prior column_position-1) )
                  group by table_name, prefix_length ) z ) z ) z
order by 1;

result:

PREFIX_LENGTH          PREFIX_SIZE            REMAINING_SIZE         DISTINCT_COUNT         TOTAL_COUNT            ROWS_PER_PREFIX        PREFIXES_PER_BLOCK     ROWS_PER_BLOCK
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1                      7                      132.854                10                     1000                   100                    1                      61.608
2                      14.5                   125.354                20                     1000                   50                     1.304                  65.200
3                      22.161                 117.693                60                     1000                   16.666                 4.129                  68.827
4                      29.961                 109.893                300                    1000                   3.333                  20.672                 68.909
5                      38.854                 101                    1000                   1000                   1                      58.575                 58.575

check:

analyze index pk_t validate structure;
select opt_cmpr_pctsave, opt_cmpr_count from index_stats;

OPT_CMPR_PCTSAVE       OPT_CMPR_COUNT
---------------------- ----------------------
13                     3

The check above roughly corresponds with the prefix length with the maximum rows_per_block in the calculation - but I suggest you check my working carefully for yourself before trusting it :)

I am assuming the table is so large that you can't just take a copy and try out different prefix lengths. Another approach would be to do just that on a sample of the data - the sample should be chosen as a random selection of prefixes for a given compression candidate (rather than just a random selection of rows)


Trying to deploy sp_blitzIndex - but fails I'm trying to deploy the sp_blitzIndex procedure from Brent Ozar. I'm running it in a query window, in SSMS and get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb..SQLServerCheckup_2A98B846_4179_496B_AAF8_60B405E3ED68_waits'.
Starting run. sp_BlitzIndex(TM) v7.99999 - December 11, 2020
This is being done on the master DB. I'm running SQL Server 2019 developer edition on my local machine.
Any suggestions on a fix?

You're trying to deploy something other than sp_BlitzIndex. The warning that you're getting is part of a paid product that we offer, and I'm guessing that you copy/pasted that script from another source.
Try downloading a fresh copy of sp_BlitzIndex from one of the official sources, like http://firstresponderkit.org or https://www.brentozar.com.


Datetime Difference in MySQL when format is Varchar I have two columns in a table: STOP and START, of type varchar.

I want to have the difference between both columns, and I'm using :

SELECT DATEDIFF(stop, start) AS difference from T1;

It is giving me null, because of different data types.

To test, I also tried:

SELECT STR_TO_DATE('5/16/2011 20:14 PM', '%c %e %Y %H:%i:%s');

It is also giving null.

What should I do for the difference? I have n number of data and time in different formats.

You really need to unify all Date and Time Stamps into one format for both start and stop.

Just to give you an example, I'll compute the time difference for the first row.

Here is the code to compute it:

SET @StartDT = 'Mon Jan 21, 21:07:47 IST 2013';
SET @StopDT  = 'Tue Jan 22, 07:59:58';
SET @YYYY_Start = SUBSTR(@StartDT,-4);
SET @MMM_Start  = RIGHT(LEFT(@StartDT,7),3);
SET @DD_Start   = RIGHT(LEFT(@StartDT,10),2);
SET @HMS_Start  = RIGHT(LEFT(@StartDT,20),8);
SET @YYYY_Stop  = SUBSTR(@StartDT,-4);
SET @MMM_Stop   = RIGHT(LEFT(@StopDT,7),3);
SET @DD_Stop    = RIGHT(LEFT(@StopDT,10),2);
SET @HMS_Stop   = RIGHT(LEFT(@StopDT,20),8);
SET @MM_Start   = RIGHT(LEFT((LOCATE(@MMM_Start,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
SET @MM_Stop    = RIGHT(LEFT((LOCATE(@MMM_Stop,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
SELECT @YYYY_Start,@YYYY_Stop,@MM_Start,@MM_Stop,@DD_Start,@DD_Stop,@HMS_Start,@HMS_Stop,@MM_Start,@MM_Stop;
SET @YYYMMDD_Start = CONCAT(@YYYY_Start,'-',@MM_Start,'-',@DD_Start);
SET @YYYMMDD_Stop  = CONCAT(@YYYY_Stop,'-',@MM_Stop,'-',@DD_Stop);
SET @TS_Start = CONCAT(@YYYMMDD_Start,' ',@HMS_Start);
SET @TS_Stop  = CONCAT(@YYYMMDD_Stop,' ',@HMS_Stop);
SET @TimeDiffSec = UNIX_TIMESTAMP(@TS_Stop) - UNIX_TIMESTAMP(@TS_Start);
SET @TimeDiff = SEC_TO_TIME(@TimeDiffSec);
SELECT @TS_Start,@TS_Stop,@TimeDiffSec,@TimeDiff;

Ready to see the difference? Here it is:

mysql> SET @StartDT = 'Mon Jan 21, 21:07:47 IST 2013';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @StopDT  = 'Tue Jan 22, 07:59:58';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @YYYY_Start = SUBSTR(@StartDT,-4);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MMM_Start  = RIGHT(LEFT(@StartDT,7),3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DD_Start   = RIGHT(LEFT(@StartDT,10),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @HMS_Start  = RIGHT(LEFT(@StartDT,20),8);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @YYYY_Stop  = SUBSTR(@StartDT,-4);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MMM_Stop   = RIGHT(LEFT(@StopDT,7),3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DD_Stop    = RIGHT(LEFT(@StopDT,10),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @HMS_Stop   = RIGHT(LEFT(@StopDT,20),8);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MM_Start   = RIGHT(LEFT((LOCATE(@MMM_Start,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MM_Stop    = RIGHT(LEFT((LOCATE(@MMM_Stop,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @YYYY_Start,@YYYY_Stop,@MM_Start,@MM_Stop,@DD_Start,@DD_Stop,@HMS_Start,@HMS_Stop,@MM_Start,@MM_Stop;
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
| @YYYY_Start | @YYYY_Stop | @MM_Start | @MM_Stop | @DD_Start | @DD_Stop | @HMS_Start | @HMS_Stop | @MM_Start | @MM_Stop |
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
| 2013        | 2013       | 01        | 01       | 21        | 22       | 21:07:47   | 07:59:58  | 01        | 01       |
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
1 row in set (0.00 sec)

mysql> SET @YYYMMDD_Start = CONCAT(@YYYY_Start,'-',@MM_Start,'-',@DD_Start);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @YYYMMDD_Stop  = CONCAT(@YYYY_Stop,'-',@MM_Stop,'-',@DD_Stop);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TS_Start = CONCAT(@YYYMMDD_Start,' ',@HMS_Start);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TS_Stop  = CONCAT(@YYYMMDD_Stop,' ',@HMS_Stop);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TimeDiffSec = UNIX_TIMESTAMP(@TS_Stop) - UNIX_TIMESTAMP(@TS_Start);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @TimeDiff = SEC_TO_TIME(@TimeDiffSec);
Query OK, 0 rows affected (0.00 sec)

Final Answer

mysql> SELECT @TS_Start,@TS_Stop,@TimeDiffSec,@TimeDiff;
+---------------------+---------------------+--------------+-----------+
| @TS_Start           | @TS_Stop            | @TimeDiffSec | @TimeDiff |
+---------------------+---------------------+--------------+-----------+
| 2013-01-21 21:07:47 | 2013-01-22 07:59:58 |        39131 | 10:52:11  |
+---------------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

Even though this is right, it would be illogical to script this convoluted method for all rows, especially since some rows have different formats.

Therefore, it is in your best interest to convert start and stop columns into DATETIME or TIMESTAMP


Non clustered index : performance issue I have table which  has schema  as follows

TABLE  EMP

EMPID  - Primary KEY
DEPTID - NON Clustered Index
ISActive - NON Clustered Index

It has other non index columns as well

This table is highly transactional and will have lots of data. Currently I have 824392 rows in this table.

Now I am executing query which takes lot of time sometime and sometime it works like a charm.

PFB the query  :

UPDATE EMP SET ISActive= 0  WHERE DEPTID in (11,22)

Questions :

Will only nonclustered index on ISActive get updated?
Will all the nonclustered indexes and clustered index get updated ?
If I remove some of the rows as they are quite old will that help with performance ?
Why this query take some minutes to execute and sometimes it will take less then second ?
What is the best possible solution for this ?

Any pointers are highly appreciated

Thanks in advance

Regards,

Phani

1&2 - Every update query must update the data for the table, wherever it may appear - in the clustered index (always), in any index that has the column in its definition, and in any index which just includes this column.

3 - Removing records may or may not help, it depends on 4:

4 - Probably, because sometimes it will not use the index, but will instead choose to scan the entire table. If it's scanning the entire table, then reducing the size of the table (via (3) above) will help. It will scan the entire table if, so far as the optimizer is concerned, that will be more efficient than using the index, then accessing the clustered index to complete the operation. It may choose to do this because it's actually true that the index will not help, or if statistics are out of date, or just sometimes it gets this wrong. Examine execution plans to determine if this has happened.

4a - If there's a lot of activity on the table, then other operation may have locks that prevent your query from processing as fast as it could if it was the only activity occurring in the database. Waiting for locks can take seconds, minutes, or hours - it depends on how timely the other operations are

5 - Solution to what?

824392 isn't that many rows, but it may make for a big table - it depends on how wide those rows are, really.


What are system columns used for in PostgreSQL? I was following a PostgreSQL tutorial and the author just mentioned that there are system columns in PostgreSQL but did not mention when and why to use them.

Also, in PostgreSQL Documentation it just mentions that there are system columns but does not state when and why to use them?

What are the uses of system columns in PostgreSQL?

System columns are primarily meant to be used by the system. These columns are implementation details that might change between major versions (though very unlikely for most of them). You won't find a lot in the manual for when and why to use them because you are not supposed to use them in standard operation. The manual says:

  You do not really need to be concerned about these columns; just know they exist.

That said, there are various special situations and corner cases where each of these columns come in handy. Remember that SELECT * never includes system columns. You have to list them explicitly if you want them in the SELECT list.

For instance, you can use the ctid (tuple ID) to uniquely identify rows (within the same statement) in the absence of a primary key within the same statement:

Numbering rows consecutively for a number of tables

Search for Postgres in combination with each system column here on dba.SE or on stackoverflow to find various other use cases. Example search.

In addition to the manual page about system columns you are linking to, there is at least one other that's relevant:

https://www.postgresql.org/docs/current/static/storage-page-layout.html


Settle debate between Professor and Myself on interpreting Crow's Foot Notation in an ERD I am taking an introduction to database objects course at college.

I am in disagreement with the professor over how to interpret the Crow's Foot Notation for Cardinality in an ERD diagram.

Example: Look at this image (one I pulled from lecture slides):

According to my interpretation, this indicates that:

A Supplier may have 0 or many Shipments
A Shipment may belong to only one Supplier
A Customer may have 0 or many Orders
An Order may belong to only one Customer

However, according to my instructor's interpretation, this shows that:

A Shipment may have 0 or many Suppliers
A Supplier can have only one Shipment
A Customer may have only one Order
An Order may have many customers

I have been unable to find any evidence to contract my own understanding of this... but, again, I'm just a student. Maybe I'm missing something? If I am, I'd like to figure out where I went wrong.

Your professor is wrong.  The cardinality in almost all versions of crows foot notation is read such that the cardinality next to the entity is the cardinality of that entity.  You can also see by the fact that common sense tells you that whoever drew this ERD did not intend what your professor is reading.

I will say that I have (very rarely) seen people read cardinality the way your professor is doing it.  I think of it as "looking down the relationship" at the other side.  The fact is that this approach doesn't make sense for readability in my opinion.  It's also just not the way the vast majority of people do it.

You're better off to stick with the conventional wisdom on this.  If you can't convince your professor, then go with the flow until the end of the semester, but then forget this inappropriate convention and do it the way almost everyone else does from then on.

As an aside/pro-tip: when you see many/optional cardinality, I'd suggest you get in the habit of reading it "zero or more", not "zero or many".  I think "zero or many" kind of implies "not 1", which isn't the nature of such cardinality.


SQL server not using the memory allocated We have noticed that for one of our SQL server with below configuration ,memory is not getting availed as allocated.

  SQL server 2005 SP4, 32 bit

  Windows server 2003 R2 SP2, 32 bit with 16 GB of RAM and 12 Cores

Also, we have enabled AWE and PAE as well.

Min server memory = 1024

Max server memory = 13500

I know Task manager is not a best way, but RAMMAP is also not working, but we believe SQL is not using the allocated memory.

Please advise what can be done here or is my AWE not properly configured?

You have a odd system. Your database size is 350+ G and you have 32 bit system  I would say this is a system which I would never like to have in my environment. Its very difficult to manage 350 G database on 32 bit SQL Server which has VAS limit(by default) of 2 G. You are bound to face memory pressure going ahead.

AWE in 32 bit system only allows SQL Server to use memory beyond its VAS capabilities and that too only data and index pages can take advantage of extra memory by AWE. Plan cache, procedure cache and other caches cannot utilize this memory.

  I know Task manager is not a best way, but RAMMAP is also not working, but we believe SQL is not using the allocated memory

This is not clear, what do you mean by believe, can you show some facts how you reached to conclusion that SQL Server is not using memory. Please run

DBCC MEMORYSTATUS()

command and share output on some shared link(onedrive,dropbox...) and post link into your question so that we can see how much memory SQL server is using.

Edit:

  But when i looked on task manager and process explorer for private bytes, memory never got exceeded to 300-500 MB.

This is the incorrect thing you are doing. Task manager is not a place to see SQL Server memory utilization we have dbcc memorystatus output and perfmon counters to see SQL Server 2005 memory utilization. If you note task manager show Process private bytes this memory is pageable but memory allocated via AWE API in 32 bit is Locked and non pageable and non shareable. So task manager is not showing you TOTAL memory utilized by SQL Server as it is not tracking memory allocated via AWE. So I guess you got the difference now. Please read AWE how it functions in 32 bit.

Now from dbcc memorystatus dump you posted

Memory Manager   KB
VM Reserved     1632608
VM Committed    169000
AWE Allocated   13762560
Reserved Memory 1024
Reserved Memory In Use  0

Memory utilized by SQL Server 2005 would be sum of VM committed and AWE Allocated. This would not give you absolute value but is correct. There are some more allocations which are not included in this but that would be just few MB and would hardly matter

So memory utilized by SQL Server is (169000+13762560)KB which is approx 13G. Which matches with max server limit you have set. So don't worry SQL Server is using memory allocated to it.

Plus whatever may be result please upgrade to 64 bit version of both Windows server and SQL Server


Compare an hour between two hour columns Please help. I am kinda new to this database world. I am using PostgreSQL.

I have a table with defined shifts like this:

CREATE TEMPORARY TABLE shifts AS
SELECT id, start_hour::time, end_hour::time
FROM ( VALUES
  ( 1,'06:00:01','14:00:00' ),
  ( 2,'14:00:01','22:00:00' ),
  ( 3,'22:00:01','06:00:00' )
) AS t(id,start_hour,end_hour);

I have been using the between operator to compare a given hour with the start_hour and end_hour so I can get the corresponding shift, like this

SELECT * from Shifts S where given_hour BETWEEN start_hour AND end_hour;

It works when it is shift 1 or 2 but not with shift 3. It returns no value. Can you please give me a little advice on how should i do this?
Thank you!

You cannot have an interval defined by an "end" smaller than your "start". Your shift 3 does not work because you're crossing day boundaries, and your "end" (6 o'clock, is smaller than your end 22 o'clock).

If you want to define your shifts by hours, you would need to have, in general, 2 intervals per shift, and shift 3 should have:

+----+--------------+------------+--------------+------------+
| id | start_hour_1 | end_hour_1 | start_hour_2 | end_hour_2 |
+----+--------------+------------+--------------+------------+
|  1 |     06:00:00 |   14:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  2 |     14:00:00 |   22:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  3 |     22:00:00 |   24:00:00 |    00:00:00  |   06:00:00 |
+----+--------------+------------+--------------+------------+

And you should rewrite your query with:

SELECT
    *
FROM
    Shifts S
WHERE
       (given_hour >= start_hour_1 AND given_hour < end_hour_1)
    OR (given_hour >= start_hour_2 AND given_hour < end_hour_2) ;

As a little advice, I would  use BETWEEN .. AND .. only with discrete variables (basically integers and texts). With continuous variables (and time is continuous), it's safer to include one of the ends and exclude the other (>= AND <):  given_hour >= start_time AND given_hour < end_time. That's why I changed :01 by :00 to work this way (and avoid problems with 14:00:00.123, which could eventually happen).


SQL DB After Restore Script execution I have a SQL Database that is being restored every day from a Production SQL server.

The restore is being done by a third party application. Once the DB is restored I need to set some specific user permissions and also run other script and I would like to do that exactly after the restore process has been complete.

  Is there a way I can execute a script after a DB has been restored?

Thanks

Looks like one cannot set up trigger on RESTORE DATABASE event

I can think of

Set up a regular SQL job to check/add permissions if needed
As this link, add a TRIGGER on [msdb].[dbo].[restorehistory]?


How to monitor Database usage I've run into an issue with one of my tableau users. His queries have taken down the DB a few times yesterday.
My solution is twofold.

First, I'd like to limit his rights. Is there a way for me to limit the maximum length of his queries before they timeout?
Second, I'd like to setup an alert for the database. I know how to setup an alert, however I'm not sure what thresholds to use.

Is there some way for me to see recent usage in order to estimate where I should set the threshold?

Thanks!

This sounds like a job for SP_BLITZ,SP_WHOISACTIVE etc. By Brent Ozar & Team.

https://www.brentozar.com/first-aid/

Edit, I'm not advertising for them (it's a free resource anyway).  This is exactly what I've used for this exact scenario.  3rd party apps and their rogue developers killing the DB.

You might also want to limit them to using views/procedures that you write/have control of in order to get their data.

Edit2: And I just realized this is an old question that got bumped somehow.
My answer stands.


PostgreSQL multiple column foreign key I've got the table "session" with

CONSTRAINT u_session UNIQUE (where, who, when)

I need to use that combination of (where, who, when) as the foreign key in table "results". How can I do it?

A foreign key can also constrain and reference a group of columns. As usual, it then needs to be written in table constraint form. Here is a contrived syntax example:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

Of course, the number and type of the constrained columns need to match the number and type of the referenced columns.

Ref : https://www.postgresql.org/docs/current/ddl-constraints.html


Selecting single row based on a condition Consider the following table structure

Table Structure

 Table "public.studenttable"
 Column  |  Type   | Modifiers
---------+---------+-----------
 id      | integer | not null
 uid     | text    | not null
 subject | text    |
Indexes:
    "studenttable_pkey" PRIMARY KEY, btree (id, uid)

select * from studenttable

 id | uid | subject
----+-----+---------
  1 | S01 | Maths
  1 | S02 | Science
  1 | S03 | English
  2 | S02 | English

My objective is to get rows which have only English as a subject which should return id = 2 row only. I have following approaches in mind

1) Return subjects as a string separated by semicolon for particular id and run a loop to match violations.

select id,(array_agg(subject),';')
from studenttable
group by id

2) The second approach is to do a self join on studenttable to make sure that id  = 1 has only one subject.

I am not able to form the final query. Also is there any alternative?

SELECT id, uid
FROM studenttable
GROUP BY id, uid
HAVING SUM( CASE WHEN subject='English'
                 THEN 0
                 ELSE 1
            END ) = 0


Different implementation of query I have the following query. I use phpmyadmin for testing and it executes perfectly. However when I try to use it in my server application I always get null on rank

SET @_c = 0;
SELECT kills,deaths,revives,damage,levels,rank,skill
FROM (
    SELECT
        *,
        (@_c \:= @_c + 1) AS `rank`,
        ((kills + damage + levels)/(deaths+revives+1)) AS skill
    FROM leaderboard
    ORDER BY skill DESC) AS newtable
WHERE soldier_id = 1(example)

I am looking either for another implementation or idea how to fix it.

Edit: I put (example) because soldier_id can be 1,2, or 3000. It not constant that what i am trying to say. And i have to use : because i use hibernate in java which requires it.

try..

SET @_c = 0;
SELECT kills,deaths,revives,damage,levels,rank,skill
FROM (  SELECT  *,  (@_c \:= @_c + 1) AS `rank`,
        ((kills + damage + levels)/(deaths+revives+1)) AS skill
    FROM leaderboard   ) AS newtable
WHERE soldier_id = 1(example)
ORDER BY skill DESC;

check for NOT NULL values in cols: kills, damage, levels, deaths, revives


MongoDB cannot be started after moving the server I had a happy MongoDB 3.0.5 installed on a VM on a Windows 2012R2 Server, for light use. I moved the VM to a new physical server without any major changes, and to my surprise it cannot be started; and I can't seem to be able to find the issue on the internet.

The error I'm getting is:

2016-07-09T02:46:26.936+0430 W STORAGE  [initandlisten] database D:\mongodata MYDBNAME could not be opened due to DBException 10080: ns name too long, max size is 127 bytes
2016-07-09T02:46:26.940+0430 I STORAGE  [initandlisten] exception in initAndListen: 10080 ns name too long, max size is 127 bytes, terminating

The database name is a few characters and reasonable, and it used to work. I tried upgrading MongoDB to 3.2.3; didn't work but the error is changed:

2016-07-09T03:54:25.404+0430 I -        [initandlisten] Assertion: 10078:nsToDatabase: db too long
2016-07-09T03:54:26.117+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\util\stacktrace_windows.cpp(174) mongo::printStackTrace+0x43
2016-07-09T03:54:26.118+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\util\log.cpp(136) mongo::logContext+0xb0
2016-07-09T03:54:26.118+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\util\assert_util.cpp(222) mongo::msgasserted+0xfa
2016-07-09T03:54:26.118+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database.cpp(429) mongo::Database::getCollection+0x1ab
2016-07-09T03:54:26.119+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database.cpp(182) mongo::Database::_getOrCreateCollectionInstance+0x35
2016-07-09T03:54:26.119+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database.cpp(214) mongo::Database::Database+0x25b
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database_holder.cpp(134) mongo::DatabaseHolder::openDb+0x380
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(337) mongo::repairDatabasesAndCheckVersion+0x517
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(559) mongo::_initAndListen+0xee4
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(659) mongo::initAndListen+0x27
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(892) mongoDbMain+0x216
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(698) wmain+0x35
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten] mongod.exe    f:\dd\vctools\crt\crtw32\dllstuff\crtexe.c(623) __tmainCRTStartup+0x10f
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten] KERNEL32.DLL BaseThreadInitThunk+0x22
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten]
2016-07-09T03:54:26.123+0430 I STORAGE  [initandlisten] exception in initAndListen: 10078 nsToDatabase: db too long, terminating
2016-07-09T03:54:26.133+0430 I CONTROL  [initandlisten] now exiting

I tried running mongod --repair without any luck, it doesn't start at all. I can't have any logical explanations about what could have happened, since everything else on the server is fine.

Here's the exact changes made to the machine:

Host name is changed
Machine is dis-joined and then joined to the domain controller
The IP address of the network is changed

I'll be thankful to know what could have caused this, and how can I fix it.

From the error it looks like you are hitting the mongodb's namespace limit 128 bytes.

One of the namespace; may be db.collection.index is 10078 is too long thus causing this issue.


table structure for events Got a bit stuck, need a hand.

I need to store events at the database, and events may happen at

different location
multiple locations
at the same time

so these are my tables

CREATE TABLE location(id, name, address)

and

CREATE TABLE event (id, name, date)

Where should I add the location in this design?

The simple (id, name, date, location_id REFERENCES location) won't do, because one event may happen at different locations and I don't want to store 2+ rows of the same event since event is unique.

Hmm... I'm a bit confused. By definition, if two events happen at different places, then they're different events.

My advice in this scenario is to tave 3 tables as follows:

CREATE TABLE Event
(
  Event_ID INTEGER,
  Event_Description INTEGER,  -- FK
  Event_Location INTEGER,  -- FK
  Event_Date DATETIME
);

And then two other "parent" tables

CREATE TABLE Event_Description
(
  Description_ID INTEGER PRIMARY KEY, -- FK in Event table.
  ED_DESCRIPTION VARCHAR(50));
);

CREATE TABLE Event_Location
(
  Location_ID INTEGER PRIMARY KEY,  -- FK in Event table
  Location_Description VARCHAR(50)
);


AlwaysOn Availability Groups Delayed Replica With AO SQL 2012, is it possible to configure one of the member as delayed replica? I know ideally we would like this to be synced but in case of human error, it would give us the ability to check on the secondary and recover the uncommitted data from the secondary. In one of our earlier setup we used Log shipping where the restore was delayed by an hour to one of the secondary server. Hence during any issues with data, we were able to recover the data faster instead of restoring the whole database and recovering to the point of time. Any input is appreciated...Thanks in advance

No, this is not possible with Availability Groups.  Any incurred delays are usually from things like network, secondary replica log block flush, etc.  There's no way that I can think of to turn that knob and create more of a time gap.

It sounds like if you want to take this "delayed transactions" approach, Log Shipping may have actually been the best solution there.  Or you could just deal with point-in-time restores in the (hopefully corner) case of data manipulation errors.  Ideally that wouldn't be happening all that frequently, and you should already have point-in-time restores as part of your operational capability, so you wouldn't need to incur any other additional technology of processes to accomplish this.


SQL Server INNER REMOTE JOIN returns more rows than INNER JOIN I'm trying to join a few rows from a remote view to a local table. The view has about 300 million rows, so I want to use the REMOTE query hint so that all 3 million rows don't have to be transferred to my computer.

SELECT R.Something, L.ID, L.Something
FROM [dbo].[LocalTable] L
INNER JOIN (
    SELECT TOP 100 Something, L_ID FROM [RemoteServer].[RemoteDB].[dbo].[RemoteTable]
    ) R
ON L.ID = R.L_ID

This returns 100 rows, as I expected, and takes basically no time, as I expected.

However,

SELECT R.Something, L.ID, L.Something
FROM [dbo].[LocalTable] L
INNER REMOTE JOIN (
    SELECT TOP 100 Something, L_ID FROM [RemoteServer].[RemoteDB].[dbo].[RemoteTable]
    ) R
ON L.ID = R.L_ID

starts to return thousands of rows. I quit it after a few seconds, but it was in the tens - hundreds of thousands.

How could a query hint change my result set?

TOP 100 with no ORDER BY means it is undeterministic which 100 rows from the remote table end up participating in the join. This is execution plan dependant and can vary.

If it is a one to many relationship it may be the case that one batch of 100 rows has more matches on the other side of the join than another different batch of 100 rows.

You should specify an ORDER BY (inside the derived table) on some unique column or combination of columns to ensure deterministic results.


What is best practice to organize / normalize database tables? I manage group that uses a database that is about 12 years old.

There are more than 200 tables / views that have been created by various people over the years, many of them no longer with us.

Most of the table and field names are descriptive, but with the number of table/fields we've lost track of what is used for what.  Consequently, whenever a new process is created, we create new tables rather than looking for the data in existing tables so the problem just gets bigger.

I'm looking for suggestions on documenting, normalizing and getting the multiple tables under control. Any comments, recommended books, articles, etc would be appreciated.

Database refactoring is worth reading.

I think a sane procedure would follow these steps.

Audit
Analyze
Deprecate
Revoke
Remove
Kill

Audit

Determine which tables, views, stored procedures, etc., are still being used. Determine which applications are accessing your database.  There's more than one way to do that, and the best way depends in part on your dbms.

Auditing can hurt performance. This will take time; faster hardware might be justifiable.

Analyze

Analyze the results of your audit. Distinguish currently used database objects from seemingly unused database objects. There's no such thing as "definitely unused database objects"; you might have a table that's used only once a year for some kind of accounting process.

Deprecate

Decide which objects to end support for, and deprecate them. Communicate this change both widely and loudly.  Remain open to changing your mind when you find you've stepped on someone's toes.

Revoke

Revoke permissions on formerly deprecated database objects. If you've overlooked an application or user, they'll eventually complain. You might want to approach this in several steps, spread out over time.

Remove

Remove the formerly deprecated database objects in such a way you can easily replace them.

Kill

Finally, kill the unused database objects. Breathe easier.

Each step takes time. Allow more time than you think you need.


MongoDB 2 node cluster I am trying to understand the implications of setting up a 2 node MongoDB cluster with the following configuration (Documentation does say a minimum of 3 nodes is required).
MongoDB Version is 3.2.
If I configure both nodes with "arbiterOnly": false, and set Node1 with Priority: 2 and Node2 with Priority: 1, in case Node1 goes down, won't Node2 take over? Why not?

Because MongoDB automatic failover works election system. When the election system wants to decide a new primary node, it needs a qualified majority from secondary nodes or arbiter.
If you want to run a replica set of just two nodes, you should use the arbiter. The arbiter node resource cheaper than the other nodes.
Check out PSA in the document.
Priority means;

The value of the member’s priority setting determines the member’s
priority in elections. The higher the number, the higher the priority.

Imagine, running with 3 nodes replica set, one of them going down, and the other two nodes hast to decide a new primary. When they doing that check out their priority.

The priority settings of replica set members affect both the timing
and the outcome of elections for primary. Higher-priority members are
more likely to call elections, and are more likely to win. Use this
setting to ensure that some members are more likely to become primary
and that others can never become primary.

Otherwise, you should take a look at this document.


Max from 2 columns with priority date col then the seq col Oracle I have a column xdate, column seqnumber, personID

I want 1 record per personID, where it is the most recent xdate max(xdate)
if there is multiple same max xdates i want it to look then for the max seqnumber to determine what row to pull.

obviously this doesn't work:
select max(xdate), max(seqnumber), personID From table1
Group by personID

1 person can have many records, 1 person can have the many of the same xdate, but seqnumber iterates for each xdate starting with 0 going to n

I can't quite figure this out, I can't use a correlated sub-query because there are many records.  I would need to join I believe.

Example data above, I would want the results of the

1st row
3rd row
6th row

Please help,
mike

You don't mention DBMS, so I'll assume it is one that supports window functions (DB2, Oracle, SQL-server, Postgres, etc)

select personid, xdate, seqnumber
from (
     select personid, xdate, seqnumber
          , row_number() over (partition by personid
                               order by xdate desc, seqnumber desc) as rn
     from T
) as X
where rn = 1;

Edit: added sample data and result (http://sqlfiddle.com/#!4/73ad2/4)

create table T
(   personid   int
,  xdate      date
,  seqnumber  int );

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

insert into t values (123, '2012-01-01', 0);
insert into t values (123, '2011-01-01', 0);
insert into t values (136, '2012-01-02', 1);
insert into t values (136, '2012-01-02', 0);
insert into t values (136, '2012-01-01', 0);
insert into t values (168, '2012-01-04', 2);
insert into t values (168, '2012-01-04', 1);
insert into t values (168, '2012-01-04', 0);

select personid, xdate, seqnumber
from (
    select personid, xdate, seqnumber
         , row_number() over (partition by personid
                              order by xdate desc, seqnumber desc) as rn
    from T
) x
where rn = 1;

PERSONID    XDATE   SEQNUMBER
123     January, 01 2012 00:00:00+0000  0
136     January, 02 2012 00:00:00+0000  1
168     January, 04 2012 00:00:00+0000  2


Which field should be the candidate for index in this case? You're tracking visits already in another table that contains quite some information about the visit.

In order to block duplicate visits after a certain amount (Let's say 5) you think its best to use a separate table that only contains the IP address and the date of the visit.

Which field should be indexed? IP address or Date? Both will be used in the query when you check for duplicate visits by that IP on a certain date.

Using both the fields will be an overkill? Is there any tool that can help with this estimation?

Table

CREATE TABLE `ip_visit`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` int(10) unsigned NOT NULL,
`date` date NOT NULL,
 UNIQUE KEY `id` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1

This table is expected to receive 100k entries per day.

In order to make this blisteringly fast, what I would do is the following - with the proviso that I know nothing about your server RAM, CPU or disk config.
Bear in mind that a decade's records will be ~35M/year - so ~350M for a decade - most servers should be able to cope with a relatively small table like this - but again, I don't know what else is happening on your server.
1. Do not call a field "DATE" - it's an SQL keyword and this can

make debugging difficult, and,
make porting your app to a different server difficult should you choose to do so.

2. I would make id (perhaps visit_id would be better?) the PRIMARY KEY of the table - you already have practically that situation anyway.
Calling all tables' PRIMARY KEY "id" can again make debugging tricky, since you won't know which table's id field is causing any issues.
3. I would put an INDEX on both ip and visit_date - make any queries very fast - 100K/day ~ 1.2 inserts a second which any reasonable server should be able to easily cope with.
4. Your data is essentially historical.  Rename the table above to current_vist. Then you can create an EVENT (and/or Google "How to create MySQL events") to fire at, say, 03:00 on Sunday morning (or whenever your system is quiet - in conjunction with your backup schedule).
Just a word about backups - always backup all of your data - it obviously does't need to be to hand for every record, but I'd keep it anyway. A gzipped mysqldump will be tiny in any case.
5. Have this event add summary data for the last day (say, Saturday at midnight), week, month, even year) to some summary tables.
6. Then delete from current_visit from Saturday at midnight - that way, you have at most (if you do this weekly) 700K records in your active current_visit table. This will make backups very quick - and inserts also.
7. You can then perform summary queries over days, months, years by summing over the the different tables for your dates of interest. You can also create VIEWs for those periods of interest.
8. Depending on your backup strategy and/or your other server activity, you can use your backups to take the summary data to another machine and perform historical queries at your leisure without stressing your production server.
9. Another use case is the ARCHIVE engine which is designed for non-updatable data - I know it's not your case, but it might be of use for others? I haven't used it, so I can't day - motto is test, test and test again!


Managing failover for MySQL nodes using HA Proxy We have an S1<-M1<->M2->S2 setup of MySQL replicated nodes. These are now to be brought to the back-end of an HA Proxy server to split read from writes. We also intend to achieve automatic fail-over with this. However, write requests to be routed to M2 only when M1 fails. In usual scenario, we will be good with not touching M2 at all. There seems to be no "balance" option in HA Proxy that switches to M2 only when M1 fails.
Please suggest how this can be achieved using HA Proxy.

Writing round-robin across M1 and M2 is a time consuming solution to be taken up at this point in time.

There is a 'backup' option that you can use in HAProxy that does what you are after. e.g. if M1 fails, use the the backup (M2)

https://www.haproxy.com/blog/failover-and-worst-case-management-with-haproxy/

Then once M1 is available again, it redirects back to M1.

n.b. you do of course need to think about replication delays, as you don't want to redirect back to M1 if it is an hour behind M2 when it comes back up. We use something like this to achieve it:

http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/


Could not complete cursor operation because the table schema changed after the cursor was declared I get this error message occasionally when I execute a stored procedure that pulls information from multiple linked servers and aggregates the data. It looks similar to what I have below.

DECLARE Cur CURSOR FOR SELECT name FROM sys.servers WHERE is_linked = 1 AND (@Environments IS NULL OR name IN (SELECT Item FROM clrStringSplit(@Environments, ',')))
OPEN cur
FETCH NEXT FROM cur INTO @Environment
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY

    SET @Query = '
        EXEC ' + @Environment + '.db.dashboard.storedproc ''' + CAST(@StartDate AS VARCHAR(10)) + ''', ''' + CAST(@EndDate AS VARCHAR(10)) + ''''

    INSERT INTO #Table (Environment, Column1, Column2, Column3, Column4, Column5, Column6, Column7)
    EXEC(@Query)
END TRY
BEGIN CATCH
    PRINT 'There was an error trying to query against ' + @Environment
    PRINT ERROR_MESSAGE()
END CATCH

FETCH NEXT FROM cur INTO @Environment
END

The stored procedure it calls on each server is the exact same on each one and is a basic select statement that pulls from tables underneath the dbo schema. It will "randomly" error out with the message of "Could not complete cursor operation because the table schema changed after the cursor was declared." Sometimes it runs fine, other times not. Each time its ran, even if back to back will sometimes work and sometimes not. What could possibly cause this to happen and why is it so intermittent? The error can be generated on any given call to a procedure on one of the servers. It's not always the same server it tries to query against that produces the error.

It is worth mentioning that I have several every procedures that do something extremely similar but never produce the issue. But they are all under the same schema (dbo) and this is the first time where a different schema is used.

EDIT - I forgot to mention that I had tried using a FAST_FORWARD cursor and it does not seem to help.

Try explicitly setting your cursor to be LOCAL STATIC FORWARD_ONLY.

I don't know what specific changes might be happening on your linked servers, or what that stored procedure might be touching, but static makes a copy of the data so that the cursor isn't impacted (well, doesn't care about) any of those changes.

This is the same issue with sp_MSforeachdb that causes it to skip databases. They use the wrong kind of cursor there and, thanks to error handling, databases that have been touched in any way since the cursor opened just get silently skipped. This is why I wrote a replacement that is also available - with some handy improvements - in Brent Ozar Unlimited's First Responder Kit.

As an aside, there is a better way to execute dynamic SQL on databases/servers supplied by a variable. This usually makes it possible to use sp_executesql and pass strongly typed parameters (instead of doubling up quotes all over the place, making messy conversions of ints and dates to strings, and so on).

DECLARE @exec nvarchar(512);

... while loop ...

SET @exec = @Environment + N'.db.sys.sp_executesql';

INSERT INTO #Table
(
   Environment, Column1, Column2, Column3,
     Column4, Column5, Column6, Column7
)
EXEC @exec N'EXEC dashboard.storedproc @StartDate, @EndDate;',
   N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate;


Partitioning MySQL for "expired" transactions to improve performance I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly designed -- string keys, denormalized and so on, and performance is poor.

In the cases I am considering, the records could be considered "expired" based on some business logic, and while the data is needed for historical and statistical transactions, at runtime, operations need only apply to unexpired rows.  Unexpired rows account for something like 5% of the whole data set.

It's runtime performance I want to improve.

We're not in a position to redesign the system yet, but it occurs to me that some combination of partitions or perhaps conditional indexes might help.  For example some data expires monthly, and we could run a monthly task to mark the records expired in a column of the table.  One table has a date column which appears to have been designed for this purpose, although it is not even indexed!

So if our runtime queries are scoped to include a condition like "expired_on is not null" my hope is that we could get near-steady-state performance using an index ... But is this a good case to use either or both of a conditional index (only unexpired rows) or partitions, perhaps creating a new one each month to include the prior month's now-expired records?

We could create some sort of archiving system, or roll up tables for historical stats, but for now I am looking to buy time :-). Any suggestions greatly appreciated.

If a lot of your queries have conditions similar to what you describe, e.g. range condition on the date column:

WHERE dateColumn >= (CURRENT_DATE() - INTERVAL 1 MONTH)

WHERE dateColumn >= '2012-01-01'
  AND dateColumn < '2013-01-01'

it will be useful to define the primary key of the table as (dateColumn, tableAI), where tableAI is an auto incremented integer column.

For InnoDB tables, the above choice means that this index will be the clustered index of the table and any range (on the date) queries on the table will use it and will only scan the relevant part of the table.

If the HasExpired condition is more complex, e.g. some rows should be marked expired after a month, some others after a year, some after a few days, I would consider splitting the table(s) into 2 parts. One having only expired data and one having all data.

So, when some data expires, you'll simply delete them from the "not_expired" table. The data will already be on the other. When you search for not expired data, you'll be searching only one table (the smallest one).
You also have the benefit of adding different indexes on the tables (for example only the above suggestion for the CI in the "all-data" table and many indexes on the not-expired table, depending on your queries.) Without the split, you would need to have an HasExpired column and attach it to all your indexes (as first column), to have equally good indexes.

If the (not-expired) data is updated many times, it might be beneficial to have the split as "not-expired" and "expired" data. So you won't need to do any updates on the "expired" table. But when some data expires, you'll simply move them (instead of deleting) from the "not_expired" table to the "expired" one.


is it possible to run postgres with no WAL files being produced? I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced.

We have a end of day batch process which generates a large number of WAL files (and has filled the disk a couple of times). We also take a pg_dump backup both before the start of the end of day process and also at the end. We are happy that should there be a problem during the batch process to restore the pre pg_dump backup.

When i set the wal_level to minimal we are still getting a large number of WAL files due to the type of database updates which occur. So is it possible to have no WAL files as all (basically a wal_level=none)?

thanks
dave

Short version: no. PostgreSQL doesn't support generating no WAL whatsoever, because changes to system catalog tables in the pg_catalog schema are always logged. There's also transaction ID logging (pg_clog), the multixact tracking data (pg_multixact), etc, in addition to the xlog, but they tend to be very small.

As Daniel says, you can run with minimal WAL generation by:

Using wal_level = minimal
Creating all tables as UNLOGGED or TEMPORARY tables

In this case, you will also want to set fsync = off, full_page_writes = off and synchronous_commit = off, since you have no requirement for durability and crash recovery.

Of course, if anything goes wrong (like power loss, unplanned restart, DB server crash, pg_ctl -m immediate stop, etc) your data will be totally unrecoverable, but that seems to be what you want.

The rate of WAL creation and rotation with this configuration will be negligible for most applications.

If you don't mind having to re-initdb after any restart (even a clean one) you could put pg_xlog on a tempfs, but I doubt it's worth the hassle when Pg will generate only a tiny bit of WAL.

You should also check and make sure you don't have WAL archiving (archive_mode = on) enabled, that wal_keep_segments isn't set. Both of those are only useful if you're doing WAL-based backup or replication. If you don't, then WAL should not accumulate, it should be rapidly recycled. Maybe your checkpoints are set to be much too infrequent, so much so that you run out of disk? Check to see if the checkpoint_segments parameter is really really high.

I think you're trying to solve the wrong problem here. The issue isn't stopping PostgreSQL from producing WAL, it's figuring out why it piled up in your batch jobs.

See also: Optimizing PostgreSQL for fast testing.


BCP did not preserve relational data I developed a document tracking database and front end C#/.NET app on my local machine (SQLServer Express and VS Express).  Once it was working and usable I had our IT branch install the database (SQL Server Express) on the main server, and pointed the app to it instead of my local version.  I then used bcp to export from my box and import to the server.  I noticed that the parent child relationships are now all broken.  Each table has an identity column called UID, which is referenced in child tables.  But the UIDs did not copy as they were on my machine, rather they were all recreated upon insert.  I have not found any documentation on the proper way to handle this, but now the data is basically unusable.  I can easily redo the BCP, but can anyone point me to my mistake?

Here is what I used for the export (both the data and format just in case):

bcp DocControl.dbo.Documents out Documents.dat -n -S MYMACHINE\SQLEXPRESS -T
bcp DocControl.dbo.Documents format nul -T -n -f Documents.fmt -S MYMACHINE\SQLEXPRESS

And for the import (also, before import I had duplicated the schema, so the tables, keys and stored procedures were all recreated ahead of time, but without the data):

bcp DocControl.dbo.Documents in Documents.dat -n -S SERVER\SQLEXPRESS -T

This is just for one of the tables, but the other tables were done exactly the same way.  Now the Documents table UID starts at 1 and goes up, whereas previously it started at something like 380 and went up from there.  So the referring IDs in child tables no longer point to the right parent.

You're lacking the -E flag for identity columns, which allows you to preserve the values instead of re-seeding them on the import.

bcp DocControl.dbo.Documents in Documents.dat -n -E -S SERVER\SQLEXPRESS -T

From MSDN,

  [-E] Specifies that identity value or values in the imported data file
  are to be used for the identity column. If -E is not given, the
  identity values for this column in the data file being imported are
  ignored, and SQL Server automatically assigns unique values based on
  the seed and increment values specified during table creation.

  If the
  data file does not contain values for the identity column in the table
  or view, use a format file to specify that the identity column in the
  table or view should be skipped when importing data; SQL Server
  automatically assigns unique values for the column.


Help with COUNT across multiple columns I've had a look around and a play and can't what I need to work so wondered if you could help?

I have four rows in a table and I wish to count the number of items across all four. So to make it clearer the table looks like this:

Table: 'faults'
Row 1: affected_device_1
Row 2: affected_device_2
Row 3: affected_device_3
Row 4: affected_device_4

Each row can be populated with an entry. I wish to collate and put into a report the top five "affected devices" so that someone higher up can look at it and make a decision. So in plain English the result would return something like:

The top five affected devices are:

X
XX
XXX
XXXX
XXXXX

The SQL code to create the table is as follows:

CREATE TABLE `faults` (
  `fault_id`int(6) NOT NULL AUTO_INCREMENT,
  `affected_device_1` varchar(50) NOT NULL,
  `affected_device_2` varchar(50) NOT NULL,
  `affected_device_3` varchar(50) NOT NULL,
  `affected_device_4` varchar(50) NOT NULL,
  PRIMARY KEY (`fault_id`))

Then I insert a bunch of data into each of the four columns:

INSERT INTO `faults`
(
    `fault_id`, `affected_device_1`, `affected_device_2`,
    `affected_device_3`, `affected_device_4`
)
VALUES
(1, 'device1-name', 'device2-name', 'device3-name', 'device4-name'),
(1, 'device3-name', 'device1-name', 'device2-name', 'device4-name'),
(1, 'device4-name', 'device3-name', 'device1-name', 'device2-name'),

Each 'fault' can have up to four 'affected devices' associated with it. I want to then count what are the top 'affected devices' across all four columns. Which devices occurs the most and is therefore most problematic.

Sorry if I haven't made it any clearer. The problem I suspect is that the design is rubbish so please bear with me.

Given the new information (you should probably edit the question, not add an answer, but I assume you are not entitled to). First I don't understand the database design, do you always get 4 affected devices per fault? Anyhow, you can transpose your table like:

select affected_device_1 from faults
union all
select affected_device_2 from faults
union all
select affected_device_3 from faults
union all
select affected_device_4 from faults

I won't be efficient but it is the best I can think of. From there you can count the number of occurrences for each device:

select affected_device, count(1)
from (
    select affected_device_1 as affected_device from faults
    union all
    select affected_device_2 as affected_device from faults
    union all
    select affected_device_3 as affected_device from faults
    union all
    select affected_device_4 as affected_device from faults
) as T
group by affected_device;

Note that if a fault contains the same device twice the count will be wrong.


Can we change the default owner of mongodb to other than 'mongod' user? mongod user is created on the system during installation and all of the appropriate files are created and owned by the mongod user.

Can we change the default owner of mongodb to other than 'mongod' user ?

Where to change ?

One of the Government Client wants to change as to tighten security.

Please share your ideas.

Yes! In /usr/lib/systemd/system/mongod.service. Changing User parameter value to wanted user name. Remember to change Group too and then chown -R all files and directories to that new user.

[Unit]
Description=High-performance, schema-free document-oriented database
After=network.target
Documentation=https://docs.mongodb.org/manual

[Service]
User=mongod
Group=mongod
Environment="OPTIONS=--quiet -f /etc/mongod.conf"
ExecStart=/usr/bin/mongod $OPTIONS run
ExecStartPre=/usr/bin/mkdir -p /var/run/mongodb
ExecStartPre=/usr/bin/chown mongod:mongod /var/run/mongodb
ExecStartPre=/usr/bin/chmod 0755 /var/run/mongodb
PermissionsStartOnly=true
PIDFile=/var/run/mongodb/mongod.pid
# file size
LimitFSIZE=infinity
# cpu time
LimitCPU=infinity
# virtual memory size
LimitAS=infinity
# open files
LimitNOFILE=64000
# processes/threads
LimitNPROC=64000
# total threads (user+kernel)
TasksMax=infinity
TasksAccounting=false
# Recommended limits for for mongod as specified in
# http://docs.mongodb.org/manual/reference/ulimit/#recommended-settings

[Install]
WantedBy=multi-user.target


SQL Help - Exclude data with just one distinct value I have the following query:

select agentname,CallCenterID, count(leadid) Dials
from LeadCallHistory
where agentname not in ('vdad','system')
    and cast(callCenterStartTime as date) between cast(getdate()-1 as date)
        and cast(getdate()-1 as date)
group by agentname, CallCenterID

What is the best way to exclude rows of data where a CallCenterID field only has 1 unique Agentname?  For example if the query were to return:

agentname    CallCenterID   Dials
jjones       999            100
jjones       1000           500
crobin       1000           1000
jjones       1001           500
crobin       1001           500
swhite       1002           1000

In this example I would like to exclude the data for CallCenterID's 999 and 1002 because they only has 1 unique agentname

One way to do this:

with stats as (
  select agentname,CallCenterID, count(leadid) Dials
  from LeadCallHistory
  where agentname not in ('vdad','system')
    and cast(callCenterStartTime as date) between cast(getdate()-1 as date)
    and cast(getdate()-1 as date)
  group by agentname, CallCenterID
)
select * from stats s1 where exists (
  select 1 from stats s2
  where s2.CallCenterID = s1.CallCenterID
    and s2.agentname <> s1.agentname
)


Distributed Transactions between SQL Server 2000 & MySQL Stopped Working We have been successfully copying data from a MySQL Slave database into a SQL Server 2000 database. The MySQL server is a linked server. I have tried using v3.51 and v5.1.8 of the ODBC connector provided my MySQL.

Simply trying to insert into a temporary table in SQL Server from a table in MySQL using OPENQUERY causes the following error:

  Server: Msg 7391, Level 16, State 1, Line 1
  The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
  [OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver]Optional feature not supported]
  OLE DB error trace [OLE/DB Provider 'MSDASQL ' ITransactionJoin::JoinTransaction returned 0x8004d00a].

This error started happening when replication between the MySQL master and slave failed. This has since been fixed but the error above persists.

Is there something more that needs to be done on the MySQL side? Any suggestions for the SQL Server side? I have already tried restarting DTC as well as rebooting the server.

Thanks

Someone posted a solution to a related problem on StackOverflow:

https://stackoverflow.com/questions/5945435/sql-server-2000-linked-server/6651704#6651704


€ Euro sign/symbol not showing up on Oracle 10gr2 and aix 6.1 this is Oracle 10gr2 on aix 6100-02

The issue is that I do not manage to see the € sign from Oracle.

This is an easy way to see the issue:

SQL> insert into prueba values ('€');
SQL> commit;
SQL> select * from prueba;
EURO
------------------------------------------------------------
¿
SQL>

This are the tests I have already done.

I see it from the shell:

oracle@krats:/u01#perl -Mcharnames=:full -CS -wle 'print "\N{EURO SIGN}"'
€
oracle@krats:/u01#

But I do not see it from the sql  :

SQL> select chr(128) from dual;

C
-

SQL>

This database has the following NLS_SESSION_PARAMETERS;

SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   SPANISH
NLS_TERRITORY                  SPAIN
NLS_CURRENCY                   ¿
NLS_ISO_CURRENCY               SPAIN
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              SPANISH
NLS_SORT                       SPANISH
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              ¿
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 filas seleccionadas.

SQL>

I have create a table just to show this up.

oracle@krats:/u01#NLS_LANG=SPANISH_SPAIN.UTF8
oracle@krats:/u01#export NLS_LANG
oracle@krats:/u01#sqlplus "/ as sysdba"
SQL> create table prueba (euro nvarchar2(20));
table created.
SQL> insert into prueba values ('€');
1 fila creada.
SQL> commit;
Confirmación terminada.
SQL> select * from prueba;
EURO
------------------------------------------------------------
¿
SQL>

Finally, I'm not sure if important but this are the oracle user env vars:

SQL> host env;
_=/usr/bin/env
LANG=en_US
LOGIN=oracle
PAGER=/usr/bin/pg
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/u01/bin:/usr/bin/X11:/sbin:.:/u01/oracle/product/10.2/db_1/bin/
NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1
ORACLE_BASE=/u01/oracle/product
LC__FASTMSG=true
EDITOR=/usr/bin/vim
KM_CRYPTO_LIB_PATH=/usr/lib/libkmcryptolib.so
LOGNAME=oracle
MAIL=/usr/spool/mail/oracle
LOCPATH=/usr/lib/nls/loc
ORACLE_SID=DIESEL
PS1=oracle@krats:${PWD}#
USER=oracle
AUTHSTATE=files
ORA_NLS10=/u01/oracle/product/10.2/db_1/nls/data
SHELL=/usr/bin/ksh
ODMDIR=/etc/objrepos
R_SHLIB_LD_LIBRARY_PATH=/usr/lib
HOME=/u01
FCEDIT=/usr/bin/vi
TERM=vt100
MAILMSG=[YOU HAVE NEW MAIL]
ORACLE_HOME=/u01/oracle/product/10.2/db_1
PWD=/u01
TZ=Europe/Madrid
KM_SUPPORT_LIB_PATH=/usr/lib/libkmsvcshlib.so
ORACLE_BIN=/u01/oracle/product/10.2/db_1/bin/
A__z=! LOGNAME
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat

SQL>

Has anyone faced a similar situation?

Many thanks.

Your database character set is WE8ISO8859P1, it doesn't have the € sign. Either use NVARCHAR2 or change your database character set (export, reinstall with a character set that supports the euro sign like WE8ISO8859P15 or AL32UTF8, and import).

Here's an example of NVARCHAR2:

SQL> SELECT * FROM nls_database_parameters WHERE parameter LIKE '%SET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               WE8ISO8859P1

As you can see I am in the same situation as you.

SQL> CREATE TABLE prueba (id NUMBER, euro NVARCHAR2(20));

Table created.

SQL> INSERT INTO prueba VALUES (1, N'€');

1 row created.

SQL> INSERT INTO prueba VALUES (2, UNISTR('\20AC'));

1 row created.

SQL> INSERT INTO prueba VALUES (3, NCHR(8364));

1 row created.

SQL> INSERT INTO prueba VALUES (4, CHR(8364 USING NCHAR_CS));

1 row created.

SQL> INSERT INTO prueba VALUES (5, '€');

1 row created.

SQL> SELECT id, euro, ascii(euro) FROM prueba;

        ID EURO                 ASCII(EURO)
---------- -------------------- -----------
         1 €                           8364
         2 €                           8364
         3 €                           8364
         4 €                           8364
         5 ¿                            191


adding 2nd catalog for linked server to DB2 I am unable to figure out how to add multiple catalogs for a DB2 linked server.
I have an initial Linked server setup and working properly, but I am struggling to figure out how to add multiple databases from the DB2 instance (version 11.1), using below:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)   Jun 17 2016 19:14:09   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor) .

From my experience there can be just 1 catalog per 1 linked server

You will need to create separate linked servers for each database on remote server (DB2) that you want to be available for querying on your "local" server (SQL Server)


Turn TDE off when restoring SQL databases We are just getting started with TDE.  Eventually it will be in our Prod SQL Server environment and two major non-prod environments, but currently it is only in one non-prod system.

My question is about restoring these databases.  We have several other environments (Enterprise and Standard Edition) and an offsite vendor that we sometimes send database backups to.  For those, we will either have to use an intermediary server to turn TDE off before restoring, or back up with different keys.

But specifically, I am wondering how to restore from a TDE enabled prod environment to a TDE enabled non-prod environment.  Once a TDE database is restored over without TDE being turned off, is it using the source server's keys going forward?  I am wondering if turning TDE off and on (which would take hours) would be a requirement in this situation.
Also note the strange behavior when I restored below.  It appeared that it is carrying a historical key through a previous incarnation of the database, and when copying to a server with the same database name, is allowing the second server's key.  It this correct?

I did the following in our non-prod environment consisting of two database instances on different VMs:

On instance 1:

Enabled TDE on the database DBA,
Backed up the cert, key, and database,
Restored the backup of DBA to a new database DBA2,
Backed up the database DBA2,
On instance 2:
Copied the DBA2 backup from instance 1,
Restored DBA2.

Note that I did not create any certs or keys for DBA2, not did I copy any DBA certs and keys from instance 1 to 2.

Instance 2 has its own unrelated DBA database with TDE certs and keys.  TDE is not enabled on DBA2 after restore.

So it seems like:

the instance 1 DBA key got carried over into DBA2 in step 3, and
the restore may have used the DBA key from instance 2 in step 6, even though instance 1's DBA and instance2's DBA are separate entities.

Here is the encryption status on these databases:

    SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
    FROM sys.dm_database_encryption_keys
where db_name(database_id) like 'DBA%'

Instance 1:

dbname  encryption_state    percent_complete    key_algorithm   key_length
DBA     3                   0                   AES             256
DBA2    3                   0                   AES             256

Instance 2:

dbname  encryption_state    percent_complete    key_algorithm   key_length
DBA     3                   0                   AES             256

But specifically, I am wondering how to restore from a TDE enabled prod environment to a TDE enabled non-prod environment.

There is an entire Docs article on this. Let us know if you have specific questions.

  Once a TDE database is restored over without TDE being turned off, is it using the source server's keys going forward?

You'll see, in the article above, that yes you won't be able to read the database unless the server certificate which is protecting the database encryption key (DKE) which resides in that database has been restored and available on the destination server.

  I am wondering if turning TDE off and on (which would take hours) would be a requirement in this situation.

If the customer can't use TDE, then it's a requirement (Non-Enterprise Editions). If you don't want to send them your internal certificate, then once restored to the destination server, rotate the server certificate to a new one. It won't require any extra operations (apart from metadata changes) on the database. you could then send the customer the cert + the database, in two separate communications channels or a secure channel, of course.

  Also note the strange behavior when I restored below. It appeared that it is carrying a historical key through a previous incarnation of the database, and when copying to a server with the same database name, is allowing the second server's key.

Huh? Your output doesn't show anything but the encryption state of the database. You quite literally can't restore the database without the server certificate. So if it let you restore the database without any issue and you didn't restore a server certificate for it, then the logical conclusion is that the current server certificate is the same on the source and destination server, which is a security no-no.


Key-range RangeI-N lock compatibility in SQL Server The documentation states that the RangeI-N lock (Insert range, null resource lock; used to test ranges before inserting a new key into an index) is compatible with itself (see the compatibility matrix), so even though one transaction has obtained a RangeI-N lock on particular key another transaction can also obtain such a lock.

Further below, it says that

  When inserting a value within a transaction, the range the value falls
  into does not have to be locked for the duration of the transaction
  performing the insert operation. Locking the inserted key value until
  the end of the transaction is sufficient to maintain serializability.
  For example, given this INSERT statement:

  INSERT mytable VALUES ('Dan');

  The RangeI-N mode key-range lock is placed on the index entry
  corresponding to the name David to test the range. If the lock is
  granted, Dan is inserted and an exclusive (X) lock is placed on the
  value Dan. The RangeI-N mode key-range lock is necessary only to test
  the range and is not held for the duration of the transaction
  performing the insert operation. Other transactions can insert or
  delete values before or after the inserted value Dan. However, any
  transaction attempting to read, insert, or delete the value Dan will
  be locked until the inserting transaction either commits or rolls
  back.

Quoting another source - Microsoft SQL Server 2008 Internals: Transactions and Concurrency:

  For example, the RangeIn-Null lock is acquired when SQL Server
  attempts to insert into the range between keys in a session using
  Serializable isolation. This type of lock is not often seen because it
  is typically very transient. It is held only until the correct
  location for insertion is found, and then the lock is converted into
  an X lock.

My understanding is that this type of lock is during the process of identifying the range where the newly inserted key should be placed (I'm assuming that's what 'test the range' means). After this happens the lock is released, the new key gets inserted and an X lock is placed on it.

However I do not see why two RangeI-N locks are said to be compatible with each other. If transactions A and B both place a RangeI-N lock on the same key because they both want to insert a new key into the range and transaction A performs the insert first, then the location for key insertion determined by B might be already incorrect because the ranges have changed (A inserted a new value there). Could anyone explain that?

My understanding is that this type of lock is during the process of
  identifying the range where the newly inserted key should be placed
  (I'm assuming that's what 'test the range' means). After this happens
  the lock is released, the new key gets inserted and an X lock is
  placed on it.

RangeI-N lock is requested to test that insertion into key range will not interfere with another transaction that may potentially do some serializable operation incompatible with insertion (like reading for example).

During insertion of record into index database engine locates page, where key being inserted should be placed. Page and key locks are not used at this stage yet, only latches. Once page is located, engine starts acquiring locks necessary for performing insertion of record to the page. These are IX lock for the page located, then RangeI-N lock for the key next to key being inserted. Upon acquiring RangeI-N is not held, and next X lock for the key being inserted is requested. After necessary locks acquired, insertion of the record proceeds.

Say we have table and data

CREATE TABLE T
(
    K int NOT NULL,
    CONSTRAINT UQ_T UNIQUE CLUSTERED (K)
);

INSERT INTO T (K)
VALUES (1), (2), (9), (10);

Let's take result of the following query

SELECT K, %%lockres%% AS lockres
FROM T;

which is

K    lockres
---- ---------------
1    (8194443284a0)
2    (61a06abd401c)
9    (30b7763ed433)
10   (d08358b1108f)

(we will refer to it in the things that follow).

Then, say we have a query

SELECT COUNT(*)
FROM T
WHERE K BETWEEN 1 AND 9;

Running it under SERIALIZABLE isolation level results to the following locking footprint

To protect key range, database engine locks every key in this range with RangeS-S lock. Additionally, key next to the range is locked (this is necessary for non-unique index, but engine is doing it for unique index too). In this case, locks are placed on resources corresponding (according to result of the query taken earlier) to keys 1, 2, 9 and 10.

Non-concurrent insertion to the table

INSERT INTO T (K)
VALUES (3);

results to following locking footprint

Key 3 does not exists in the table, and so database engine requests RangeI-N lock on the resource (30b7763ed433) corresponding to key 9, which is next to 3 at the moment. Acquiring RangeI-N means that there is no incompatible serializable activity being performed, so engine proceeds and acquires X lock on the resource (98ec012aa510) corresponding to key 3. Then actual insertion happens and locks are released. Notice that there is no release of RangeI-N lock.

Running above SELECT and INSERT concurrently causes INSERT to wait until transaction performed serializable SELECT is active

because of RangeI-N incompatibility with RangeS-S. As soon as transaction performed SELECT ends INSERT will proceed.

This is what "test the range" means.

  However I do not see why two RangeI-N locks are said to be compatible
  with each other.

RangeI-N locks compatibility is good, because of it allows concurrent insertions into key range.

Imagine we are doing

INSERT INTO T (K)
VALUES (4);

and

INSERT INTO T (K)
VALUES (6);

concurrently.

It may happen that at some moment both statements acquire RangeI-N on the resource (30b7763ed433) corresponding to key 9. But this is fine, because of then concurrency is controlled by X on the keys and by page latch further.


SQL Server audit query that fires a trigger Using SQL Server, does exist a way to audit from inside a trigger the sql that fires it?

I need to know the SQL query that fires a trigger over a database without a profiler.

Thanks

I have a number of triggers that do this, and I find that DBCC INPUTBUFFER is generally the best way to do it. Caution: the output is limited to 4000 characters. Very long queries will be truncated.

DECLARE @sql nvarchar(max)
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS nvarchar(100)) + ')'
CREATE TABLE #SQL (
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
)
INSERT INTO #SQL
EXEC sp_executesql @sql

SELECT @sql = EventInfo FROM #SQL
DROP TABLE #SQL

At the end of this, @sql contains the query for the current request. Also, you could just as easily use a table variable instead of a temp table.


SQL Server: what is cleared by reboot? Weird things happened lately on one of my SQL Servers:

Select * from View never ends, but the SQL queries inside the view runs well separately.
ALTER procedure never ends.
DELETE procedure cause Error:1222, which suggests a lock from something
Multiple SQL jobs never end once started.(killed manually after run for 3+ days)

I had a feeling that something was not right with the SQL server, so I rebooted the server and everything was fixed.

I still don't know what went wrong. It is like SQL Server wore out. I am wondering if anyone had similar experience or can point me some reading about what happen during the process of rebooting.

You can run this script if it happens again:

SELECT pr.spid, pr.loginame, pr.status, pr.blocked, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
        ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
        ON txt.objectid = obj.object_id
WHERE pr.blocked <> 0
    and pr.spid >= 50

It should give you a list of processes which were blocked. After that you can try to find the process which blocks all of these processes. You can try (if you have the right for it) to kill the process using kill yourSPID.

But beware, just try the kill if the spid is above or equal to 50! Otherwise it's an internal process!

After a restart every process is either rolled forward or rolled back. Your tempdb is recreated and all internal processes are restarted.


Adding constraint to an existing table column I have a table that doesnt have any constraint on them like this

CREATE TABLE SomeTable
(
   Id int NOT NULL,
   Type varchar(50),
)

insert into SomeTable values (12,'Exchange-Student');

Now I know that I can do alter into add constraint and then check if type is of my desired value.
I just dont know how to implement it correctly.

For example lets say that I want it to be restricted to 3 types 'Exchange-Studen', 'Independent student' and 'program Student'

How would I implement that ?

To alter ypour existing table use

ALTER TABLE SomeTable
ADD CONSTRAINT `Student_type2` CHECK (Type IN
('Exchange-Student','Independent student','program Student'))

Or ad following to the CREATE TABLE seperated by comma

CONSTRAINT `Student_type2` CHECK (Type IN
('Exchange-Student','Independent student','program Student')


MySQL Index Creation Internals Right now there are two ways to build an index of a table in MySQL:

Create the table structure first, then import the data and add indexes later.
Create the table structure with indexes and then import the data.

In the first process we will have contiguous data (all fields) pages and then we have index pages. So when we query using index, MySQL has to first load the index pages and find out the matching keys and have to look up those primary key on the data pages. For this it has to then again load the data pages to get the data. This is useful when we have larger index scans as we have all index loaded contiguously.

In the second way of index creation the filtered index page will most probably contain the data page near to it as they were created at the same time. So I guess look up will be faster for a small range scans.

Is my understanding correct?

UPDATE:

I should have mentioned that the "PRIMARY KEY is enabled" (an auto increment id column) in the first way of  importing data. So internal rowid is not generated and lot of IO is saved as we are not going to add a PRIMARY KEY.

As you noted there is fragmentation when we import data using the Second method.

Considering my requirements is for larger range scans (scanning ~100M rows) I guess I will go with the first way of importing data.

update on Jun 8 11:30

CREATE TABLE `table_dummy` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `column1` bigint(20) DEFAULT NULL,
  `column2` bigint(20) DEFAULT NULL,
  `column3` bigint(20) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `column4` tinyint(1) DEFAULT NULL,
  `column5` tinyint(4) DEFAULT NULL,
  `column6` bigint(20) DEFAULT NULL,
  `column6_created_at` datetime DEFAULT NULL,
  `column7` int(11) DEFAULT NULL,
  `column8` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `twtaccount_id_2` (`column1`,`column2`),
  KEY `twtaccount_id` (`column1`,`created_at`,`column5`),
  KEY `twt_user_id` (`column3`,`created_at`,`column5`),
  KEY `original_status_id` (`column6`,`column3`,`followers_count`)
)

ENGINE is INNODB

this table has about 800M records and I took a dump and imported it in two ways.

Method 1: table was created with just the primary key and entire data was imported. Then I gave an alter statement to add the remaining indexes.

Method 2: The table was created with indexes and the dump was done. This took much larger time.

P.S The size of the resulting table through 'Method 1' was lesser by ~30GB when compared to size of table dumped through 'Method 2'. Method 1 took very less time than 'Method 2', almost twice the speed.

My primary concern is the performance of the table when I make a select  scanning a wide range of index ('index only scan').

There are a few things I can clarify for you here:

Yes, it is a good practice to delay secondary index creation until after you import the data (starting from MySQL 5.5 - not before).  Mysqlpump does this by default.
When you delay secondary index creation, internally MySQL will read, sort and then create the index (reducing fragmentation).  For MySQL 5.7 there are additional optimizations here.
When you trickle load indexes, they may have more page splits and a lower page fill efficiency (fragmentation).  This depends a little on the data, and if it is in order.  You are not giving me many clues with column1, column2, but say for example column1 was a timestamp it would probably be in order.
Pages in an index are logically in order, not necessarily physically.  This distinction may matter if you can not fit your working set for ranges in memory and are using spinning disks for backing storage.  It's also a little hard to answer because with spinning disks you are probably using RAID with some stripe size, and the blocks at the filesystem level may also be non-contiguous.
Be aware also that the optimizer may not consider the range scan for large ranges over a tablescan (since prior to 5.7 the cost model assumed pages were not in memory).  If you can rely on this being the case, you may want to FORCE INDEX and compare actual run times.  (More info on new cost model.)


Configure SQL Server Database for High Availability & Warehousing I have 3 DB servers (DB1, DB2 & DB3 - all on Win 2008 R2 with SQL Standard 2008 R2 in the same datacenter). Let's assume AdventureWorks as an example database.

My goal:
- Setup DB mirroring with automatic failover i.e. DB2 becomes mirroring server
- Setup hourly transaction log backups for point in time recovery
- Setup reporting/warehouse environment i.e. DB3 becomes the warehouse/reporting server

Setting up DB mirroring and hourly txn log backups is easy but my question is - What are  my options for replicating the principal database to the DB3 server for reporting/warehousing needs? Can I use log shipping considering I do txn log backups?

The AdventureWorks database on DB3 needs to be in read-only mode.

EDIT: The reporting database can be up to a day behind.

Quick points:

-Don't setup a separate 'transaction log backup' job outside of the 'log shipping' architecture.  Doing so will break log shipping.  Let log shipping handle all of your transaction log backups.

-You can use log shipping in 'STANDBY' mode but note that the instance will not be accessible when the server is doing a log restore.  You can set the frequency in which to do log restores (for example, take a transaction log file backup every 5 minutes, restore to the reporting server every 4 hours) which should minimize connectivity issues.

-Paul Randall will probably kill me for this, but I have a 'mirrored' instance with a snapshot taken on it every 30 minutes.  All queries are ran on the snapshot.  I have not had a single complaint in 4 months of keeping this up.  This was a 'intermediary' step before we could setup a replicated copy but it's working very well.  Again, Paul Randall does not like this solution but it works for me thus far in this limited scenario (whilst being aware that often a limited scenario will turn out to be permanent solution).

-Have you considered a daily 'snapshot replication' job which refreshes the reporting instance?  How far behind can your reporting server be?


Custom Sequence using Trigger & Sequence in Oracle SQL I have a trigger which inserts a custom Sequence after every Parent Item is inserted. Now I have a sub-Item which is linked to the Parent Item through Parent Item Id (Sequence and Parent Item Id are 2 different column )and sub items can be more than 1. My trigger for Parent Item generates Sequence as "P2505" and I want my sub-Items Sequence should be "P2505-1", for 2nd Sub-Item it should be "P2505-2" and so on.

If the next parent Item has Sequence "P2506" then sub Item Sequence should be  "P2506-1" and so on.

This is my trigger for generating Parent Item's Sequence

create or replace trigger REF_NO_TRIGGER
    before insert on LOST_ITEM
    for each row
declare
    PREFIX varchar2(254);
begin
    select NAME into PREFIX
        from SUBCAT
        where SUBCAT.ID = :new.SUBCAT_ID;
    :new.SEQUENCE := substr(PREFIX,1,1)
      ||(:new.CATEGORY_ID)
      ||(:new.SUBCAT_ID)
      ||(:new.ID);
    end;

Parent Item Table

ID  NUMBER(15,0)
CATEGORY_ID NUMBER(3,0)
SUBCAT_ID    NUMBER(5,0)
NAME VARCHAR2(20)
SEQUENCE  VARCHAR2(22 BYTE)

Sub Item Table

ID  NUMBER(30,0)
PARENT_ITEM_ID  NUMBER(15,0)
NAME VARCHAR2(20)
SEQUENCE    VARCHAR2(22 BYTE)

CREATE TABLE FOR PARENT AND SUB ITEM TABLE

CREATE TABLE PARENT_ITEM(ID NUMBER(15) GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
CATEGORY_ID NUMBER(3) NOT NULL,
SUBCAT_ID NUMBER(5) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
SEQUENCE  VARCHAR2(22),
PRIMARY KEY (ID)
);

CREATE TABLE SUB_ITEM(ID NUMBER(30) GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
PARENT_ITEM_ID NUMBER(15) NOT NULL,
NAME VARCHAR2(20),
 SEQUENCE VARCHAR2(22 BYTE),
CONSTRAINT FK_LOST_IT_SUB_LOST_IT FOREIGN KEY (PARENT_ITEM_ID) REFERENCES PARENT_ITEM(ID),
PRIMARY KEY (ID)
);

INSERT INTO PARENT_ITEM(CATEGORY_ID, SUBCAT_ID, NAME) VALUES (2,5,'MAIN_ITEM');

The ID IS GENERATED automatically and Sequence for Parent Item is generated through Trigger.
For Sub Item I am only inserting Name and Parent Item ID and I need a Sequence for that.

As per my understanding, I need to check for the parent Item Sequence in Sub-Item table and see if there is any entry , if there is no entry with that sequence then the first sub item should be "ParentItemSequence-1" and then again count and append "-2", "-3" and so on

But I don't know how to achieve this functionality through a trigger because I have to submit all items including Parent and Sub Item at the same time.

Gap Free Sequential Number

What you seek can be referred to as a "gap free sequential number".

These types of Business Requests are known to slow down performance for the tables involved.  This is due to the required serialization on the entity that generates the number.

Parent Table

PARENT_ITEM.ID is not going to be a "gap free sequential number"; you are missing the NOCACHE option.

Even then, you can still have gaps.

Child Table

I recommend you keep CHILD_ITEM.ID as a surrogate key.  I would keep the defaults for the Identity column.  Also, you should have a separate column for the numerical value that represents "-1".  I'll call this column CHILD_NUMBER_SEQUENCE.

Now, to serialize the "number generator" for CHILD_SEQUENCE, you must obtain a lock based on PARENT_ITEM_ID.

For Oracle, the easiest way is to lock the parent row.  If the table PARENT_ITEM maintains "current child sequence number" the algorithm becomes easy since you can "lock the parent row" and "get the next value" at the same time.

Table Modifications

alter table parent_item add ( current_child_sequence_number number(5) default 0);
alter table child_item add (child_sequence_number number(5) not null);
alter table child_item add
  constraint child_item_uq1 unique (parent_item_id, child_sequence_number);

Code to generate `CHILD_ITEM.CHILD_SEQUENCE_NUMBER'

-- this locks and updates the parent row.
update parent_item p
set p.current_child_sequence_number = nvl(p.current_child_sequence_number,0) + 1
where p.id = :new.parent_item_id
returning p.current_child_sequence_number into :new.child_sequence_number;
-- make sure you check that 1 and only 1 row was updated.
-- fail if that isn't true.

Final Notes

The code is specific for single row processing.

If you want performance for bulk loading, you will have to use a staging table (eg Global Temporary Table) and a Procedure.

Again, the Business Requirement of "gap free sequential numbers" is slow by nature.  Don't expect it to be fast.


MongoDB just utilizing one CPU core Would anyone happen to know why Mongo is just using one core instead of distributing traffic on all?

I am using the PyMongo driver and MongoDB is running on a centos server.

There is no such notion as CPU "traffic". A single thread runs on a single Core. Always. While a single core can execute multiple threads and even multiple processes, a single thread can not be split to be executed among multiple cores. That would require the system to understand the purpose of the program, which would be a pretty frightening thing.

So let us assume you have 4 threads and 4 Cores. Now let us assume 3 of those threads do not have much to do (one is logging, one is listening for configuration changes and one accepts new connections and spawn additional threads dealing with those connections), but the other one really has to do some computation. The core the computational intensive thread is attached to will have a higher utilization. And this behavior is even intended – or would you want the system to slow down in accepting new connections just because somebody makes a computational intensive request?


Real-world scenarios for table with DENY SELECT and GRANT [INSERT/UPDATE/DELETE] What is real-world scenarios for this type of permissions?

May be it is generally not valid, and made by mistake?

Should this kind of permissions be avoided (or prohibited)?

There is one simple enough scenario where I could see permissions being REVOKEd/DENYed for SELECT and GRANTing INSERT/UPDATE/DELETE makes sense.

Suppose one works for an institution wheire confidentiality is crucial and any large scale publication and/or loss of user data would be disastrous. Say, a bank or a medical insurance company?

You take the agents who answer calls for these institutions.

You allow the customer to key in their details (account number).

Then, the first thing the agent sees are the security questions (mother's maiden name, first pet's name, &c.).

These questions being answered correctly, the system allows the agent to see the record of that person and that person only.

Then, the agent is allowed to UPDATE or DELETE that account (change of address, cancellation of account). An INSERT wouldn't be a problem either (new account - no risk).

This way, an agent can only see one person's account details at a time and cannot SELECT large quantities of data for potential download/malicious release for criminal/blackmail/"hacking" purposes.

My girlfriend's sister is a manager in a telecenter for a large bank and the large-scale release of data is their biggest nightmare. I discussed this with her (me from a database perspective and her from a banking one). She couldn't release details of their system, so it was like 20 questions, but the clear impression I was given is that something like this was in place.


MySQL Trigger On UPDATE "Unknown Column" Error I want to log a table when it is updating a row in which the value of the "insertDate" or "updateDate" column is older than 3 months.

I wrote this trigger:

DELIMITER ///
CREATE TRIGGER `trig_before_up_dad_records` BEFORE UPDATE ON `dad_records` FOR EACH ROW
                                                                               BEGIN

SET @beforeThreeMonth = DATE(NOW() - interval 3 month);

IF (ISNULL(OLD.updateDate) OR UNIX_TIMESTAMP(OLD.updateDate) IS NULL) THEN
    SET @lastDate = DATE(OLD.insertDate);
ELSE
    SET @lastDate = DATE(OLD.updateDate);
END IF;

IF(UNIX_TIMESTAMP(lastDate) < UNIX_TIMESTAMP(beforeThreeMonth)) THEN
    INSERT INTO three_month_ago_dad_records SELECT OLD.*;
END IF;
END;
///

When update a row where the updateDate time 2015-04-09 (or anything older than 3 months) I get the following error:

  /* SQL Error (1054): Unknown column 'lastDate' in 'field list' */

However, I didn't write lastDate as a column. Where is my error?

Get rid of UNIX_TIMESTAMP() everywhere.  DATETIME fields can be compared directly.

Once you have done that, you will probably notice the missing @ that is causing the error.

I think it can be simplified down to only:

CREATE TRIGGER ...
IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month
    INSERT INTO three_month_ago_dad_records SELECT OLD.*;
END IF;
END;


Unable to create maintenance plan When trying to create a maintenance plan locally on two prod servers through SSMS, I receive this error:

No such error occurs on my UAT system. They're all running SQL Server 2017 (14.0.3162.1), and they were all installed with the same feature set. The UAT machine is Windows 10, while the prod servers are Windows Server 2016.

Which components/features specifically need to be installed such that I can create a maintenance plan?

I believe you have not installed express edition because as far as I recall I had that error there sometime back.

Also please check if you have got SSIS component installed. I do not use maintainece plans but per my knowledge since those use part of SSIS I guess that component is needed to run the maintenance plan


Cast as INT(11) instead of DECIMAL 11,0 in a view I have a view with a column defined as:

IFNULL(priority.id, 2) priority_id, ...

where priority.id has type INT(11), however the resulting view column has type:

DECIMAL(11,0)

How do I get the resulting column to retain the INT(11) type?

Decimal(n,0) still treated as integer in mysql 5.0.3 and above.

Decimal (n,0) is a fixed point that did not has fractional part.
So it is safe.

The only different int(11) with decimal(11,0) is the maximum range of number. where as the decimal has bigger range than integer

See MySQL numeric type overview


Generating large strings for test data I was recently trying to create some large strings containing generic test data for a question here.  It seems that I used to know of a way to multiply a string.  However, I can no longer remember the syntax.

I'm looking for something like:

SELECT 'A' + ('a' * 1000) + 'ha!'

To come up with "Aaaaaaaaaaaaaaaha!"  (Well, much longer, of course.)

Is this possible in T-SQL? (Or am I thinking of some other language?)  Also, are there any other techniques to generate large strings?

You can use REPLICATE:

SELECT 'A' + REPLICATE('a', 1000) + 'ha!';