How do I backup a database from MySQL 32-bit and restore it to MySQL 64-bit? I'm currently using MySQL version 5.0.16, on my local computer (which runs a 32-bit OS), for easier access while developing my program.

Now that I'm ready to deploy my program, I'd like to test the connection over a network, and use a 64-bit server to hold the database.

Up til now, I've only used the 32-bit version, and my backup 'process' would be to copy the database folder from C:\Program Files\MySQL\MySQL Server 5.0\data and paste it onto the same directory of another computer.

I'm not sure if that would work.

I tried using both MySQL Query Browser and Command Line Client to do the following:

mysqldump -u root -proot Isys-TMS(EEI1) > isystms.sql;

But both simply return this error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -proot Isys-TMS(EEI1) > isystms.sql' at line 1"

Which is not at all helpful. (Though I'm not sure if I did it right on the command line client, as opposed to Windows' own CMD)

I'm also assuming I can simply use this .sql output on the new 64-bit MySQL, so if that's not the case, what else can I do to solve this problem?

Thanks.

Notes:

I'm using Windows 7 32-bit Pro, with MySQL Server 5.0 installed in it.
I'm planning to move the database to a Windows Server 2008 64-bit, with MySQL Community Server 5.6

EDIT: I actually used mysqldump, not mysqldumb. Quick, someone make a dumb/dump joke.

mysqldump is a separate binary and not a command within the MySQL console. Furthermore it is spelled with a p in the end (you had a b there!).

So just use your command on the ordinary command line and it should work.

> mysqldump -u root -p "Isys-TMS(EEI1)" > isystms.sql

MySQL reference manual


How to set the grow rate of database in SQLserver How to set the grow rate of database in SQLserver? My database have huge size about 42 GB but the default auto grow size is 1 M. I think it is very inefficiency for the grow size of the Database.

Right-click on your database and choose Properties.  Then choose Files.  This will allow you to change the Initial size as well as Autogrowth and file size limits for your data and log files.

You should also enable Instant File Initialization (if possible).

Check this link for some excellent guidance on autogrowth.


One instance, four clients, one is slow We have a small office setup with one Windows Server 2012 running SQL Server 2016 Express. The database is hosting files for our accountant software. This is a multi module software that runs projecting, accounting, personnel and so on.

One of the four computers connecting to this server is experiencing slower response time on read/write to the database. Network setup is similar for them all, so  I am wondering if this could be some kind of hardware failure, but I don't really have a clue.

Any thoughts before I tear open the box and start switching out components?

A follow up on this one.
Fore some reason the Server Firewall was the reason for the slow speed.

I verifyed it serveral times by turning it of and

Funny though, that it only affected one of the clients.

Anywasy, all upp and running now.
Good speed.


oracle streams - how to get last successfully applied scn for crashed apply? I have a downstream Streams replication set up between Oracle 9i (source) and Oracle 11g (destination). The process is still in developlment stage, so the whole setup is not stable yet.

Sometimes my apply procedure at destination database fails. Say, with a error like this:

APPLY_NAME                     STATUS   RULE_SET_NAME                  ERROR_MESSAGE
------------------------------ -------- ------------------------------ ----------------------------------------
DWH_APPLY_SUN                  ABORTED  RULESET_APPLY_LCR_SUN          ORA-26808: Apply process AS0K died unexp
                                                                       ectedly.
                                                                       ORA-25455: evaluation error for rule set
                                                                       : STRMADMIN.RULESET_APPLY_LCR_SUN, evalu
                                                                       ation context: SYS.STREAMS$_EVALUATION_C
                                                                       ONTEXT
                                                                       ORA-00600: internal error code, argument
                                                                       s: [knlqany2lcr235], [22629], [ORA-22629
                                                                       : OCIAnyData is null
                                                                       ], [], [], [], [], [], [], [], [], []

I need to know the last source database SCN it was able to apply before failing.

DBA_APPLY.MAX_APPLIED_MESSAGE_NUMBER shows me something only in case apply process is stopped normally.

Any ideas?

Found it.

  Low-Watermark and High-Watermark for an Apply Process

  The low-watermark for an apply process is the system change number
  (SCN) up to which all LCRs have been applied. That is, LCRs that were
  committed at an SCN less than or equal to the low-watermark number
  have definitely been applied, but some LCRs that were committed with a
  higher SCN also might have been applied. The low-watermark SCN for an
  apply process is equivalent to the applied SCN for a capture process.

  The high-watermark for an apply process is the SCN beyond which no
  LCRs have been applied. That is, no LCRs that were committed with an
  SCN greater than the high-watermark have been applied.

  You can view the low-watermark and high-watermark for one or more
  apply processes by querying the V$STREAMS_APPLY_COORDINATOR and
  ALL_APPLY_ PROGRESS data dictionary views.


Converting Heap to MyISAM issue on temp table defined as innodb I was having an issue on the aws aurora brand of mysql 5.6.  I was creating temp tables with the memory engine from some selects ... ie

create temporary table mytemp table engine memory as select * from table

Some of my queries end up creating a temptable with a few hundred thousand rows (three int columns) and in the process enter a state of "converting heap to MyISAM" which I've read is horrible for performance especially when I am using the temp tables as part of a bigger join against innodb tables later in the mysql session.  (convert to myisam, then convert back to innodb to join etc)

So, after some research I changed all my temp table engines to use innodb thinking this would resolve the problem...

create temporary table mytemp table engine innodb as select * from table

When I watch the queries go by after the change to innodb I see that some are still entering a state of "converting heap to MyISAM" .  I was under the understanding that if I define the temp table as innodb that this would not happen.

Is there something I'm doing wrong here?  IF this is a performance problem, what is the best way to deal with what I'm seeing?

"Temporary table" is an overloaded term.

CREATE TEMPORARY TABLE - a table you create and manage.
An internal temp table used in a big, complex, SELECT.

The latter case causes the message "converting heap to MyISAM".  Here's the flow:

The SELECT creates a temp table (for subquery, GROUP BY, etc),
starts it as MEMORY, then
finds that it is too big ("a few hundred thousand rows" -- bigger than both tmp_table_size and max_heap_table_size).

It is normal.  It should usually be ignored.  The temp table was never InnoDB, so no conversion there happened.  (In 8.0, tmp tables will be InnoDB, not MyISAM or MEMORY.)

If you would like to show us the query, together with SHOW CREATE TABLE and EXPLAIN SELECT, we may have tips on improving it.

If you create your own large MEMORY table, you are taking RAM away from other things (eg, innodb_buffer_pool_size), thereby slowing down the system in general.

Do not try to simulate internal temp tables in InnoDB.  There is too much baggage you can't avoid, but it can.  (Redo log, undo log, transactional semantics, etc.)


Database for live mobile tracking I'm developing an app that allows to track a mobile device instantly (live)  ...
I need an of advice. The application must send the location to a webservice that in it's turn records the received data in a database.

What would be, in your opinion, the best way to store the location values?

I'm new in using bigdata and I'm afraid that simple sql requests wont be able to do the work properly ... I imagine if there is lot of users and each user send a request each 1sec I'll have issue with the database ...

An advice ? Thank you very much

I happen to work for an IoT\M2M company and we have been using SQL Server just fine for years...  Much of the architecture is all event\transaction driven and process based on an \time\event\status and have denormalized the data for multi-purpose uses.

Now, ideally when you experience the type of growth that we have over the last few years with millions of connected devices your problem becomes one of carrier relations and how are you going to get better rates and roaming agreements from your carriers...

GPRS, SMS, Data... It all cost's but I digress. Your going to want to store LAT\LONG in datatypes of FLOAT. I think GOOGLE does it with a length of FLOAT(10,6)?

MySQL or Postgres Spatial with GIS extensions is another option.

Good Luck!


MySQL - Can we use the same temporary table name in different stored procedure? In our database, we generate statistics by periodically calling a large number of stored procedures in the event.

Considering there are two SP

auto_update_daily_users
auto_update_daily_orders

Is there a conflict when using the same temporary table name (lets say temp1)in both stored procedures?

I checked Q&A on Stack Overflow, they say the temporary table's scope is the current session. I think it means when we connect the db server with clients.

But in my case all the sp are running on the server side. So could anyone explain this?

Will the temporary table be automatically destroyed after one SP is finished? What if two SPs are running at the same time? Aren't they running in the same session?

in my case all the sp are running on the server side

Any case the SP is executed using some client connection (from Event Scheduler, for example). If they are called one-by-one from some outer SP/UDF - they are executed in the same connection, and temp table created by 1st SP exists when 2nd starts. If they are executed independently (for example, as/from 2 separate event procedures) - they are executed in different connections, and each have its own independent (local) temp table.

Server only accept queries and returns its results. Anything that sends queries and receives its results is client. See SHOW PROCESSLIST - you will see all client connections, including Event Scheduler service connection and executed events connections, separate connection for each separate event.

  Will the temporary table be automatically destroyed after one SP is finished?

Temporary tables have a connection scope. If you execute 2 SPs in two different connections, each have its own temp table with its own structure and data despite the fact that their names are the same.

If you execute one SP then another, but close connection after executing 1st and create new for executing 2nd - each SP will have its own temp table again.

But if you execute one SP then another in the same connection then temptable created by 1st will exist while 2nd start, with the data inserted by 1st. - akina


Lost my SQL Server 2005 host, SQL Server 2005 database import into SQL Server 2012? My SQL Server 2005 host died, but the database related LUNS were on a SAN and still exist.  Is there a way to use my SQL Server 2012 host to recover that database or import it in some way?

The absolute best way would be to restore onto 2012 from backups if you have them, but you can also attach the databases from the SAN files onto your 2012 instance.

Once you do that, be sure to run DBCC CHECKDB to make sure all your data is intact.


Get list of characters in column I have a database table (UTF-8) with a lot of names in different languages with lots of special characters. In another script I use these names and one part is to replace the special characters. To know all appearing characters I need to get a list of them from somewhere.

Is it possible to get a distinct list of characters appearing in a column? What would be the way in PostgreSQL?

For example I have a table with names:

id | name
-----------
1  | Peter
2  | Andrea
3  | Bob

The resulting list would be

petrandbo

The order is not important but could be alphabetical

select string_agg(c,'')
from (
  select distinct regexp_split_to_table(lower(name),'') as c
  from data
) t

The inner select generates one row for each character, and the outer then aggregates that to a long string.

If you want the characters sorted, you can use an order by for the aggregate string_agg(c,'' order by c)


SQL Server hangs on a specific record For some reason my database is hanging on a specific record:

SELECT id
FROM Foo
WHERE
    id = 40469;

Executes right away:

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

Same thing with

SELECT id
FROM Foo
WHERE
    id = 40469;

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

But when I run:

SELECT id
FROM Foo
WHERE
    id = 40470;

Server just hangs.

What could cause this?

I ran DBCC checktable and it didn't find any errors.

If a specific query hangs, it's usually due to blocking. Is it possible that someone else has an active transaction that is affecting this row (or the page or partition it's in)? When you run this query, what does it show in sys.dm_exec_requests for that spid? First run:

SELECT @@SPID;

Make note of the number, then try to run the query. In another window, run the following:

SELECT blocking_session_id, wait_type FROM sys.dm_exec_requests
  WHERE session_id = <SPID from above>;

If a session shows up in blocking_session_id, find out who they are and what they're doing.

SELECT * FROM sys.dm_exec_requests
  WHERE session_id = <blocking_session_id from above>;

DBCC INPUTBUFFER(<that spid>);

You can also check if the database has any old transactions that may be causing an issue:

DBCC OPENTRAN();


Variable substitution in psql when using \copy I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that:

  The syntax of the command is similar to that of the SQL COPY command. Note that, because of this, special parsing rules apply to the \copy command. In particular, the variable substitution rules and backslash escapes do not apply.

This seems quite definitive, however I'm wondering if anyone knows of a workaround?

You can circumvent this limitation by piping the whole command to psql:

echo "\copy tbl FROM '/path/to/myfile.pgsql'" | psql mydb


MySQL backup with no downtime I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu).

My DB is pretty large: ~250GB.

I'm looking for a backup tool/service that will do the following:

full backup
no downtime / locks on the servers
automated as possible

any suggestions?

One of the options is to have an additional slave MySQL server specifically for the backup purpose which can be stopped/locked without affecting the production DB.


MySQL -> Loop through a table, running a stored procedure on each entry I have a database with 'books' (short stories for children) and it would be extremely informative to have word counts of each word in the books.

I figured out how to get the word count for each word using:

SELECT SUM
(
    ROUND
    (
        (LENGTH(pageText) - LENGTH (REPLACE (pageText, "Word", "")))
        /LENGTH("Word")
    )
) FROM pages WHERE bookID = id;

Which works wonderfully for counting the words. BUT it requires me to go through each book, and get each word out, and run it through that function (I have it saved as a Stored Procedure.)

I have a table that contains each word, with no duplicates.

My question: is there a way I can do some kind of "for each" loop on the Words table using my stored procedure?

ie. pass the stored procedure a book ID and a word and record the result. Doing EVERY word, for EVERY book. Thus saving me a LOT of manual time... Is this something I should even be doing from the DB side? Should I attempt it with PHP instead?

Honestly any input is greatly appreciated!

Create a second procedure that uses two nested cursors.

Cursors in stored procedures allow you to do a very non-SQL-like thing: iterate through a result set one row at a time, putting the selected column values into variables and doing things with them.

They are easily misused, since SQL, being declarative rather than procedural, should usually not need "for each"-type operations, but in this case, it seems like a valid application.

Once you get the hang of them, cursors are easy, but they do require a structured approach in their supporting code that isn't always intuitive.

I recently provided some fairly standard "boilerplate" code for working with a cursor to call a stored procedure in an answer on Stack Overflow, and I'll borrow very heavily from that answer, below.

Using a cursor requires some standard boilerplate code to surround it.

You SELECT the values you want to pass, from wherever you're getting them (which could be a temporary table, base table, or view, and can include calls to stored functions) and then call your existinf procedure with those values.

Here is a syntactically valid example of the necessary code, with comments to explain what each component is doing.

This example uses 2 columns to pass 2 values to the called procedure.

Note that there events that happen here are in a specific order for a reason.  Variables have to be declared first, cursors have to be declared before their continue handlers, and loops have to follow all of those things.

You can't do things out of order, so when you nest one cursor inside of another, you have to reset the procedure scope by nesting additional code inside BEGIN ... END blocks within the procedure body; for example, if you needed a second cursor inside the loop, you'd just declare it inside the loop, inside another BEGIN ... END block.

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_proc` $$
CREATE PROCEDURE `my_proc`(arg1 INT) -- 1 input argument; you might need more or fewer
BEGIN

-- declare the program variables where we'll hold the values we're sending into the procedure;
-- declare as many of them as there are input arguments to the second procedure,
-- with appropriate data types.

DECLARE val1 INT DEFAULT NULL;
DECLARE val2 INT DEFAULT NULL;

-- we need a boolean variable to tell us when the cursor is out of data

DECLARE done TINYINT DEFAULT FALSE;

-- declare a cursor to select the desired columns from the desired source table1
-- the input argument (which you might or might not need) is used in this example for row selection

DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
 CURSOR FOR
 SELECT t1.c1,
        t1.c2
   FROM table1 t1
  WHERE c3 = arg1;

-- this fancy spacing is of course not required; all of this could go on the same line.

-- a cursor that runs out of data throws an exception; we need to catch this.
-- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
-- and since this is a CONTINUE handler, execution continues with the next statement.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- open the cursor

OPEN cursor1;

my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
LOOP

  -- read the values from the next row that is available in the cursor

  FETCH NEXT FROM cursor1 INTO val1, val2;

  IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
    LEAVE my_loop;
  ELSE -- val1 and val2 will be the next values from c1 and c2 in table t1,
       -- so now we call the procedure with them for this "row"
    CALL the_other_procedure(val1,val2);
    -- maybe do more stuff here
  END IF;
END LOOP;

-- execution continues here when LEAVE my_loop is encountered;
-- you might have more things you want to do here

-- the cursor is implicitly closed when it goes out of scope, or can be explicitly closed if desired

CLOSE cursor1;

END $$

DELIMITER ;


Slow Sum/Group By over large amount of rows Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10.

SELECT mike_romeo, SUM(hotel) as counts
FROM whiskey_lima
WHERE
    yankee = 'kilo'
AND
    whiskey_india = 'a_value'
AND
    echo = 'romeo_november'
GROUP BY
    mike_romeo;

And my table looks like this:

    Column     |            Type             | Collation | Nullable |                        Default
---------------+-----------------------------+-----------+----------+--------------------------------------------------------
 id            | bigint                      |           | not null | nextval('id_seq'::regclass)
 yankee        | mike_hotel.quebec           |           | not null |
 whiskey_india | character varying           |           | not null |
 echo          | mike_hotel.romeo_yankee     |           | not null |
 mike_romeo    | character varying           |           | not null |
 oscar         | timestamp without time zone |           | not null |
 hotel         | integer                     |           | not null |
 papa          | timestamp without time zone |           |          |
Indexes:
    "whiskey_lima_pkey" PRIMARY KEY, btree (id)
    "index_unique" UNIQUE, btree (echo, mike_romeo, yankee, whiskey_india, oscar)
    "index_query" btree (yankee, whiskey_india, echo)
    "index_on_oscar" btree (oscar)

Our EXPLAIN (ANALYSE, BUFFERS) looks like this: https://explain.depesz.com/s/4lU

GroupAggregate  (cost=77294.010..78398.740 rows=38882 width=69) (actual time=56343.468..59194.302 rows=527807 loops=1)
    Group Key: mike_romeo
    Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
  ->  Sort  (cost=77294.010..77532.650 rows=95455 width=65) (actual time=56343.461..58848.379 rows=530929 loops=1)
          Sort Key: mike_romeo
          Sort Method: external merge  Disk: 111488kB
          Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
        ->  Index Scan using mike_quebec on whiskey_lima  (cost=0.560..67104.720 rows=95455 width=65) (actual time=1.647..38814.509 rows=530929 loops=1)
                Index Cond: ((yankee = 'kilo'::mike_hotel.quebec) AND ((whiskey_india)::text = 'juliet'::text) AND (echo = 'romeo_november'::mike_hotel.romeo_yankee))
                Buffers: shared hit=61425 read=68664

Questions

What should we focus on - are there any obvious solutions?

Can we avoid the sort?

Why has the query planner underestimated the rows - could this be causing some slowness? We're trying to enable extended statistics

We're looking into increasing work_mem so that the disk sort is mitigated - would this make the whole query performant?

What else am I missing?

Thanks a lot for your help

I would try with an index like:

 echo, yankee, whiskey_india, mike_romeo

Perhaps you can replace your index_unique with:

 "index_unique" UNIQUE, btree (echo, yankee, whiskey_india, mike_romeo, oscar)

The idea is to put the predicates first in then index, followed by group by columns.


Oracle NVARCHAR2(4000) with AL32UTF8 possible? I thought, through reading items here and elsewhere, that if my Oracle database NLS_CHARACTERSET is set to AL32UTF8, I can have a column of type NVARCHAR2(4000), but when I try to create it, I get SQL Error: ORA-00910: specified length too long for its datatype.  What is wrong with what I am trying?  Are NVARCHAR2 columns always UTF16, unlike what I have read?

If your NLS_CHARACTERSET is AL32UTF8, any VARCHAR2 column will support Unicode (via the UTF-8 encoding).  In 99.9% of cases, you'd never want to create an NVARCHAR2 column.

The NVARCHAR2 column, which uses the NLS_NCHAR_CHARACTERSET which will almost always be AL16UTF16 is really only useful in a couple of cases.  The first, and by far most common, is when you have a legacy application whose NLS_CHARACTERSET doesn't support Unicode and cannot be migrated to Unicode but where you want to support Unicode data in a handful of columns.  That's not the case here.  The second would be when the database does support Unicode but you want to use the UTF-16 encoding for some reason such as the ability to store primarily Japanese and Chinese data using mostly 2 bytes per character in UTF-16 rather than 3 bytes per character in UTF-8.  But if you do that, you're still (barring 12.1 and the ability to have 32k VARCHAR2 columns) limited to 4000 bytes (2000 characters in a UTF-16 column).  Hence the error when you try to define NVARCHAR2(4000) which would need at least 8000 bytes to store 4000 characters.


Mysql sequential numbering based on MAX of another column I've a table lp_pictures which have these fields:

[id | plate_uid]
[1  | aaa]
[1  | aaa]
[1  | aaa]
[1  | bbb]
[1  | bbb]
[1  | bbb]

and i need to UPDATE the field id to becomes:

[id | plate_uid]
[1  | aaa]
[2  | aaa]
[3  | aaa]
[1  | bbb]
[2  | bbb]
[3  | bbb]

The table structure is:

CREATE TABLE `lp_pictures` (
  `id` int(5) NOT NULL,
  `plate_uid` varchar(128) NOT NULL,
  `uid` varchar(64) NOT NULL,
  `filename` varchar(128) NOT NULL,
  `extension` varchar(4) NOT NULL,
  `ip_submitted` varchar(15) NOT NULL,
  `date_submitted` datetime NOT NULL,
  `mode_submitted` varchar(15) NOT NULL,
  `ip_validated` varchar(15) NOT NULL,
  `date_validated` datetime NOT NULL,
  `user_validated` varchar(64) NOT NULL,
  `size` bigint(20) NOT NULL,
  `combination` varchar(9) NOT NULL,
  `type` varchar(15) NOT NULL,
  `oldtimer` tinyint(1) NOT NULL DEFAULT '0',
  `owner` tinyint(1) NOT NULL,
  `name` varchar(128) NOT NULL,
  `email` varchar(128) NOT NULL,
  `notify` tinyint(1) NOT NULL,
  `main` tinyint(1) NOT NULL,
  `source` varchar(512) NOT NULL,
  `comment` text NOT NULL,
  `meaning` text NOT NULL,
  `like` bigint(12) NOT NULL DEFAULT '0',
  `score` bigint(20) NOT NULL,
  `share` bigint(20) NOT NULL DEFAULT '0',
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `blocked` varchar(1) NOT NULL DEFAULT '0',
  `fb_post_id` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`uid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

The order should be assigned based on date_validated field. (The lower it to the first date).

I need an mysql UPDATE instruction.

Thanks a lot !!

STEP 01 : Create index on date_submitted

ALTER TABLE lp_pictures ADD INDEX (date_submitted);

STEP 02 : Create separate table to order the id values

CREATE TABLE lp_picture_ordering
(
    id INT NOT NULL AUTO_INCREMENT,
    plate_uid varchar(128) NOT NULL,
    uid varchar(64) NOT NULL,
    PRIMARY KEY (plate_uid,id),
    UNIQUE KEY uid (uid)
) ENGINE=MyISAM;

STEP 03 : Load the table ordering info into the ordering table

INSERT INTO lp_picture_ordering (plate_uid,uid)
SELECT plate_uid,uid FROM lp_pictures ORDER BY date_submitted;

STEP 04 : Perform UPDATE JOIN to assign id values

UPDATE lp_pictures A
INNER JOIN lp_picture_ordering B
USING (uid) SET A.id = B.id;

STEP 05 : Drop the ordering table

DROP TABLE lp_picture_ordering;

CAVEATS

CAVEAT #1

You needed to add the date_submitted index so the retrieval ordered by date would be fast

CAVEAT #2

You will only need to do STEP 01 once

CAVEAT #3

STEP 02 creates the increment values for every plate_uid. This technique is only possible with the MyISAM Storage Engine. In my earlier posts, I have suggested using an auto_increment column in a multiple column PRIMARY KEY:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Jun 10, 2012 : MySQL get next unique value without auto increment
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
Aug 28, 2013 : How to go about modeling the main object of relationship?

CAVEAT #4

In STEP 03, the id values are generated by the Storage Engine.

CAVEAT #5

The ordering table includes uid with a UNIQUE KEY so that STEP 04 can do an INNER JOIN

CAVEAT #6

When your done, no need to keep the ordering table. That's why I drop it.

CAVEAT #7

Should you ever need to do this again, just do Steps 2-5

Give it a Try !!!


incorrect result when filtering by association I have a docket, which has many entries. I want only dockets, where none of the associating entries contains the text "PENDING". For example, if docket A has 10 entries, and one of those entries' docket_type varchar field contains the word "PENDING" anywhere, then I don't want that docket.

This is my solution:

SELECT * FROM dockets
INNER JOIN entries
ON entries.docket_id = docket.id
WHERE (docket_type NOT LIKE '%PENDING%')

The problem is, while it does not retrieve entries that contain that text, it still retrieves the associating dockets, when those dockets contain other entries that do not contain that text. I don't want to retrieve the docket if one of its entries contains this text in docket_type.

How could I fix this?

You need one more join (a LEFT join with IS NULL check) or a NOT EXISTS subquery and to move the condition (reversed!) there:

SELECT * FROM dockets
INNER JOIN entries
ON entries.docket_id = docket.id
WHERE NOT EXISTS
      ( SELECT 1
        FROM entries AS e
        WHERE e.docket_id = docket.id
          AND e.docket_type LIKE '%PENDING%'
      ) ;


Setting Key Constraints using a Diagram I been using SSMS for diagramming my databases and I got used to create the key constraints with this tool. Now I started thinking, while going through related chapter of a book, "...maybe this is not such a good practice"

Is there any downside towards this practice? That is defining key constraints using the Diagram Designer on SSMS?

I would suggest NOT to use Designer in creating key constraints or doing any DDL / DML operations.

Best is to use T-SQL - much flexible, more options, more powerful and you can automate many things with it. There is a learning curve, but its worth learning TSQL rather than keep using GUI.

Also, make sure you are using the latest version of SSMS - sql server 2014. Download it from here.

Read up : Managing SQL Server via Management Studio vs TSQL Commands

e.g. From BOL

ALTER TABLE dbo.DocExc
   ADD ColumnD int NULL
   CONSTRAINT CHK_ColumnD_DocExc -----> This is your constraint
   CHECK (ColumnD > 10 AND ColumnD < 50);
GO


Analyze, Vacuum equivalent for SQL Server? In Postgres, ANALYZE collects statistics about the contents of tables in the database, and store it. These statistics are used to determine the most efficient query plan, in PG. Running analyze command improves db's performance. I want to know if there is something similar in SQL Server.

Does SQL Server have commands equivalent to Postgres' Analyze and Vacuum?

If SQL Server doesn't have the exact equivalents, are there any similar commands?

If those commands do have SQL Server versions, please explain them or post links to useful documentations.

As far as I understand, rough equivalent of ANALYZE would be UPDATE STATISTICS.

  Updates query optimization statistics on a table or indexed view. By
  default, the query optimizer already updates statistics as necessary
  to improve the query plan; in some cases you can improve query
  performance by using UPDATE STATISTICS or the stored procedure
  sp_updatestats to update statistics more frequently than the default
  updates.

Quite often automatic updates of statistics is enough, but I came across few cases when I had to do it manually to get a good query plan. One common case is when rows are added to a large table and the range of values for new rows lies outside the existing statistics histogram, the so called Ascending Key Problem.

Rough equivalent of VACUUM FULL would be commands that shrink database.

  Shrinking data files recovers space by moving pages of data from the
  end of the file to unoccupied space closer to the front of the file.
  When enough free space is created at the end of the file, data pages
  at end of the file can be deallocated and returned to the file system.

In many cases shrinking a database is a bad idea.

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

When is it OK to shrink a Database?

The feature is there, but it should be used to address some very specific problems.

As @dezso pointed out in the comments, simple VACUUM command only marks pages as free for reuse in the same table, it doesn't compact the file. SQL Server does it automatically via the Ghost cleanup background process. Actually, it is possible to turn it off. So, it looks like this Ghost cleanup process is a rough equivalent to a simple VACUUM.


Why does this PostGIS query take so long to run? I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing duplicate geometries in the process of fixing invalid geometries. However, the query takes really long to run (days). This is the SQL statement:

CREATE TABLE schema.table_geomfix AS
SELECT gid,id, ST_MakeValid(geom) as geom
FROM schema.table_polygons
WHERE geom IN (SELECT DISTINCT geom FROM schema.table_polygons);

Running the two select statements separately is quick. About 1 minute for the makevalid and 15 seconds on the distinct statement.

Why does it take so long to run the combined statement?
Any advice for improving the query in order to make it faster?

NOTE: There is an index on the geom column.

Your query is needlessly complicated and can be untangled to this 100 % equivalent one:
SELECT gid, id, ST_MakeValid(geom) AS geom
FROM   schema.table_polygons
WHERE  geom IS NOT NULL;

Neither original nor this one remove any duplicates (except by possibly removing some with geom IS NULL, but I doubt that was your intention).
Possible misunderstanding
There is a common misunderstanding concerning output column names in the WHERE clause - where you can only reference input column names. So, geom in:
WHERE geom IN ...

references the original input column not the output column (the result of ST_MakeValid(geom)).
Related:

PostgreSQL Where count condition

If your actual intention was to only keep rows where the result of ST_MakeValid(geom) is equal to any currently existing geom entry:
SELECT gid, id, ST_MakeValid(t.geom) AS geom
FROM   schema.table_polygons t
WHERE  EXISTS (
   SELECT 1
   FROM   schema.table_polygons
   WHERE  geom = ST_MakeValid(t.geom)
   );

I added a table alias and table qualification to clarify what's what.
The index you mention is only going to help with this variant.
If your actual intention was to merge duplicate rows after fixing geom values:
SELECT DISTINCT ON (ST_MakeValid(t.geom))
       gid, id, ST_MakeValid(geom) AS geom
FROM   schema.table_polygons t
ORDER  BY ST_MakeValid(t.geom), gid, id;

You did not define how to break ties and which row to keep from a set of dupes. I keep the row with the smallest gid from each set. And the smallest id from remaining dupes. Details:

Select first row in each GROUP BY group?

Aside: Calling a schema "schema" is like naming your son "son" and hoping for the best that no more sons shall arrive. In other words: don't.


Do management operations such as database copy affect our current db performance? Example:

I have a database named "myDb"

I execute on the master:

create database mydb_copy as copy of myDb

Will the performance of "myDb" be affected? Specifically the avg_read_io (data reads)?

After executing copy database operation and database export operations, this is the data that I gathered. I used:
select * from sys.dm_db_resource_stats to real time query performance information.

I used a completely isolated Premium P6 (old P3) Database.

During the create database as copy, the source database was not affected at all. That means constant 0%cpu, 0%data_io, 0%log_write. At least in premium P6.

During the database export, %cpu and %data_io oscilated both from 2% to 5%. This means export operation effectively has some performance impact (very low, at least on a premium P6).


How do I deal with the inevitable scenario when a table's bigserial runs out of ids but many of the old records are deleted? I use PostgreSQL. I have many database tables which contain data of the kind that is regularly "replaced entirely", as in, I first clear (delete) all existing records, then just insert all-new records.

Previously, I did this with:

DELETE FROM the_table;

However, this kept increasing the serial column (id) all the time, eventually leading to it reaching the maximum number. I'm still unsure what happens at that point; I hope that it just resets automatically, but I fear that it probably makes the table stop working and start spitting out errors whenever new records are attempted to be INSERTed.

To solve this for these tables, I have started doing this instead:

TRUNCATE TABLE the_table RESTART IDENTITY[ CASCADE];

This does the same thing as DELETE FROM, but also resets the serial column. Great. Exactly what I wanted.

However, I also have many tables where the data needs to be preserved, and cannot be cleared like the ones I described above. I obviously cannot run TRUNCATE TABLE on those tables, but they will also eventually reach a point where either the serial or bigserial maximum is reached.

It doesn't matter if this "probably will take years". Just knowing that this will/might one day happen is enough to give me constant stress. Is there something I can do to avoid that scenario? Does PG handle this automatically? Can it be told somehow how to behave when this inevitably happens?

Since many old records likely will have been deleted by that point, I would like it to start using those unused ids automatically. For example, if ids 0-1000 are empty when it reaches the maximum serial value, instead of failing, it would start using 0, 1, 2, 3, 4... until it gets to 1000 and then it looks ahead for any unused ids until it finds one.

If the above idea is stupid, I want to hear what you all do about this problem.

If you consume 10000 sequence values per second, you'll have consumed 10000*3600*24*365 values per year, that is 3,1536E11. At that rate, it takes a couple of million years to exhaust a bignt sequence.

So you shouldn't lose any sleep over it.


MySQL multiple index columns have a full cardinality? I have noticed indexes like:
+-------------------+------------+-----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name              | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            1 | city_id        | A         |        7851 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            2 | item_id        | A         |      266502 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            3 | vote_date      | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            4 | ip_address_str | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            5 | month          | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            6 | year           | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |

I guess since column 3-6 have a full cardinality this index is not goodly constructed, that it shall be deleted and the new indexes should be built (queries should be analyzed to see if it's (city_id, item_id) or maybe (city_id, item_id, votedate) or some other combination starting with (city_id, item_id...) ?
Correct? Or I'm getting this wrong?

As @Johnakahot2use mentioned, indexes on fields with really low uniqueness (low cardinality) is not a bad thing. It's just fields with high uniqueness are generally more efficient when indexed and stored in a B-Tree.
That being said, you don't normally write queries around the cardinality of the fields in your table (usually that's not even a luxury), you write queries for the data that's needed to complete the objective at hand, and you try to index (and design your schema) in a way that offers best performance to those use cases.
In other words, if one of your use cases is to get all ip_address_str in a specific month and year, then it makes sense to create an index on month and year (in the order of which ever field is more unique). If your use case will always require filtering on item_id then adding that to the index would certainly help it to be more performant but it's not a requirement for the index to be of help either.
In conclusion, create indexes and design the schema appropriate for your common use cases, not the queries around your data's cardinality (unless it so happens that works for your use cases).


How to update "sorting_order" column of other rows when changing one? I need to set a custom order sequence for categories when adding new ones, and changing existing ones. I believe the code and sql query will be the same in both scenarios.
In my webshop_categories-table I have a so-column (sorting order INT) that I uses to determen which order the categories should be outputed to the viewer.

Here's a simplified example of how my categories-table looks like (so = sorting_order):

id  |   name   |  so
--------------------
1   |   Shoes  |  1
2   |   Hats   |  2
3   |   Bags   |  3
4   |   Coats  |  4
5   |   Rings  |  5

When adding a new category, without defining the sorting order, I would just submit a form, and run a query like this:

$so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
$ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);

(This will add a new category, and just put it at the end. In this case the so would be set to 6)

That's the very basic way of just adding a new category. Works fine. But in most cases, when dealing with categories, one need to put it in a specific order for presentation.

But usually when adding a new category, one would want to specify where this new category should be placed in relation to the existing ones. And that's when updating the so-column becomes a brain twister for me...

My simplified form for adding a new category:

<form>
  <input type="text" name="name">
  <select name="so">
    <option value="0" selected>At the end</option>
    <option value="1">At the top</option>
    <!-- php foreach loop through exising categories -->
    <option value="<?=so?>">after <?=category_name?></option>
  <select>
  <input type="submit" name="submit_add" value="Add">
</form>

<?php
if($_POST['submit_add']){
  if($_POST['so']==0){  //  put new category at the end
    $so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
  } else {
    $so = $_POST['so'];
  }
  $ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);
}
?>

I've been through the first scenario: Not specifying a sorting order.
But when it comes to using this feature, I need to update the so for the affected categories.
If I add a new category between Shoes and Hats I would use after Shoes in the selection list - which means that this new category should get the so-value of 2. This means that it will be necessary to do a +1 to the so for Hats and all categories above.

What would be a good way of accomplishing this?

A quite equal situation to this would be when moving a category from a higher to a lower so number. One would need to make space for the moved category, and bump the rest one up (do a +1 to their so). But only the ones in between the two positions ofcourse.

And it should be quite similar when doing it the other way around - Moving a category from a lower to a higher number. The only difference being that we now need to close the gap that we created between these two powitions.
If I moved Hats to after Coats - Hats would become 4, and the rest would need to be bumped one down (do a -1 to their so).

I've tried to do this by fetching the affected categories when using this feature, and update each one in a foreach loop. But I couldnt get it to work as expected for all scenarios.

Making a Copy of the all the rows (into a PHP array), Delete them, Create a new list with updated so, then Inserting them back was also a solution, but I dont think it should be neccesary just to make these changes... But that's one way I figured out that worked...

Any suggestion?

Add new category :

    $t_cat = 'jeans';//new category to insert
    $so_to_set = 2;// so for new category

First update all so greater than or equal to $so_to_set

    $query = "update categaries set so = so+1 where so >= $so_to_set";

Now insert new category with the $so_to_set

    $query = "insert into categaries(name,so) values($t_cat,$so_to_set)";

Change so for categories :

suppose we have following categaries with so

    +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  2    |
    | 2   |   shirts  |  3    |
    | 3   |   pants   |  4    |
    | 4   |   blazzers|  5    |
    | 5   |   bags    |  6    |
    +-------------------------+

    $a = 2;// so of category whose position we want to change
    $b = 5;// new so for category

    // get id of category whose position we want to change
    $temp_id = "select id from categaries where so = $a";
    // that would be 6

Now update all so between $a and $b

    if($a < $b){
        // if $a is less than $b then
        $set = "so = so - 1";
        $where = "so > $a and so <= $b";
    }else{
        $set = "so = so + 1";
        $where = "so < $a and so >= $b";
    }

    // update so's
    $query = "update categaries set $set where $where";

now update so for $temp_id

    $query = "update categaries set so = $b where id = $temp_id";

Final output of the process would be

    +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  5    |
    | 2   |   shirts  |  2    |
    | 3   |   pants   |  3    |
    | 4   |   blazzers|  4    |
    | 5   |   bags    |  6    |
    +-------------------------+


Can I on purpose lock the admin out of an Azure SQLDB (as admin) Can I on purpose lock the admin out of an Azure SQLDB (as admin)

As this may sound as a strange question on first sight. The goal is to only have sql users with data reader & writer rights on the database once the database is setup. Afterwards No accounts should exist anymore that have more rights than Reading & writing to the existing table structure. This behaviour should be irreversible.

That is not possible. From books online:

  The database user corresponding to the Server admin login cannot be
  altered or dropped.

Reference: Controlling and granting database access to SQL Database and SQL Data Warehouse


MySQL INSERT INTO SELECT vs BULK INSERTs performance Currently, I am trying to copy data from TABLE1 to TABLE2.
In terms of insertions performance, would it be the same or faster if I would to do

BULK INSERT manually (i.e BULK insert every 10K records into TABLE2 via INSERT INTO TABLE2 VALUES (1,2), (5,5), ...), versus
INSERT INTO TABLE2 SELECT * FROM TABLE1

You have to go with the BULK INSERT.
WHY NOT INSERT INTO TABLE2 SELECT * FROM TABLE1 ???
Running INSERT INTO TABLE2 SELECT * FROM TABLE1 requires a single transaction.
Imaging how populated an undo log will be to perform a single rollback.
If that transaction fails and rolls back, you create lots of table fragmentation.
Why BULK INSERT manually ???
This takes a lot of pressure off the InnoDB Storage Engine for holding large undo information.
EXAMPLE : mysqldump
Have you ever noticed when reloading a mysqldump, hundreds or thousands of rows at a time are being inserted ? If you grep a mysqldump like this:
grep "^INSERT" dump.sql

You will see many lines with INSERTs. Each INSERT is an extended insert by default. That allows 100's of rows to be inserted per INSERT command. So, the principle you already suggested of BULK INSERT 10K rows at a time is perfectly acceptable.


Why Are Table Scans Bad? At my work, I am often told that I shouldn't do a table scan because it could negatively impact the database and the app using the database. What exactly do they mean by "negatively impact"? One negative impact I can think of is that a table scan would use up a lot of the disk's time, blocking other requests from using the disk.
Is there a way to do a table scan without any negative impacts? Sometimes I need to do a table scan for one off checks (for routine things, I would of course make an index).

Table scans are not evil per se, it depends on what the query is supposed to do. If a large portion of the table is either returned to the application or used in some aggregate (like sum), it is probably most efficient to do a table scan.
If on the other hand, a small percentage of the table is to be used, looking up the rows via an index is much more efficient.
A scan uses disk resources as you mention. Another effect is that cached data in memory might have to be flushed to make room for the scan. I.e. applications may have to read data from disk instead of memory due to unnecessary scans.


Remove characters from recordset permanently I have some data in this format:

52-681-Functional-EHT
53-682-Functional-EGT
52-683-Functional-EZT
51-684-Functional-EYT
59-685-Functional-E5T

And I want to remove the interior '-Functional' permanently with a sql statement so that my records would now look like...

52-681-EHT
53-682-EGT
52-683-EZT
51-684-EYT
59-685-E5T

All my research leads me to Replace but that seems to just be on a select and can't quite figure out how to form it into an appropriate update statement. Again, I want to remove the string FOREVER.

Creation script:

create table part (
id int identity(1,1) PRIMARY KEY,
Partname nvarchar(50))

insert into Part (Partname)
values ('52-681-Functional-EHT'),
('53-682-Functional-EGT'),
('52-683-Functional-EZT'),
('51-684-Functional-EYT'),
('59-685-Functional-E5T')

Is there a reason this simple update doesn't work for you?

update part set partname = replace(partname,'functional-','')

select * from part


SQL Server script to delete accounts no longer in Active Directory We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy Database Wizard from creating these accounts on the new server.

Is there a script or some automated way of deleting the accounts that no longer exist in our Active Directory?

EDIT: Just to be clear, the logins needing to be deleted are on SQL Server 2000, which does not support the DROP LOGIN command.

Separately, manually deleting the logins in SQL Server 2000 would (I think) be done with exec sp_droplogin 'loginname' but on mine, the login name cannot be found, whether I use 'domain\loginname' or 'loginname'

Just to add to the confusion, exec sp_revokelogin 'domain\loginname' does appear to work.

EDIT 2: Finally resolved the issue. Many of the logons that were problematic were programmatically added to the database, and while they worked in the sense that a user could connect, the username vs NT login name had a mismatch of domain-prefixed logons when SQL Server expected no domain, and vice versa.

To resolve this, I modified the sp_droplogin procedure to take out one of the checks that was erroring.

I'm accepting my own answer as it works in SQL Server 2000.

What I ended doing is listing the accounts with:

    exec sp_validatelogins

And running

    exec sp_dropuser loginname
    exec sp_droplogin loginname

on the results.


The smallest backup possible ... with SQL Server Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever.

We don't mind if our backup process takes a bit longer; as it stands we need to move 30gigs of compressed backup across the WAN that takes over 10 hours.

There are 2 options we have to get smaller daily backups.

Log shipping, which would mean we would have to restructure DR process.
Strip information out of the db and rebuild on the other side (drop non clustered indexes, pack clustered indexes at 100% - rebuild on the other side)

Both would involve a fair amount of work from our part. We are using SQL Server 2008 pro, all backups are compressed.

Are there any commercial products that can give us similar backup size to option (2)?

Is there a comprehensive script out there that will allow us to accomplish (2)? (handling indexed views, filtered indexes, foreign keys and so on)

First thought based on comments...

Use differential backups every, say, 6 hours, to reduce the size/time of backup + FTP. Then reduce your full backup + FTP to weekends only. This avoids complexity of log shipping, simple to do, and only adds slight complexity to DR

I feel that differential backups are overlooked... I've suggested using them before:

How to back up a small database in SQL Server 2008 R2 Express Edition
Using DIFF backups to solve this

  I'd like to keep everything as simple as possible from a recovery perspective whilst minimising the amount of lost data in the event of a failure

Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios
Migrating large database
Using DIFF backups to speed up a backup/restore server migration

Edit: after jcolebrand's comment I'll attempt to explain more

A differential backup only takes pages that have changed. Outside of any index maintenance (which can affect a lot of the database), only a few % of pages will change during a day. So a differential backup is a lot smaller than a full backup before any compression.

If you have a full backup, say weekly, you can then do daily differentials and ship them off site. A daily full backup with differentials will still require both files off site.

This should solve the problem of getting data from A to B, C and D quickly.

You probably need to restore both the full and latest differential to get the latest data but you can maybe work around this with NORECOVERY and a STANDBY file (I haven't tried it with a diff restore for years since I was last in a pure DBA job).

An added bonus is that diff backups are unrelated to ongoing log backups so you can separate any High Availability/DR requirement from the "get data to the code monkeys" requirement.

I see some issues if you have daily full backups by policy or audit, but the diff restore can be applied before any log restores to shorten recovery time. Unlike backups, diff and log restores do interact.

Hope I've covered most bases...


Sharing a mysql database remotely I want to create a database and share the username and password with a friend who wants to access it remotely over the internet. I have mysql workbench installed but don't really know how to share a db remotely. Step by step instructions would be helpful.

He needs to connect with the hostname (or ip address) of your machine (the MySQL server).

Port 3306 needs to be open so he can get to MySQL.

Can't provide step-by-step without knowing OS of both machines, network connection between the machines, client he will be using, etc, etc.


What is an effective way to wipe sensitive data on a data pages after dropping a table's column? After dropping a column with sensitive data from a SQL Server table, the data still exists on the data and index pages.  To ensure the protection of this data, it needs to be removed from the pages as well.

For SQL Server (in particular, SQL Server 2012), what is an effective and efficient way to remove all of the data from the actual pages?

If the solution causes data to be moved to new/recycled pages, do the all the original pages get overwritten?  For pages that are recycled for rebuilding, this would obviously be the case.  For pages that were part of the table/indexes will they be wiped or have the sensitive data removed in some other manner?

No, sorry, there is no feature for reinitializing empty pages or extents when they are unused.

It is true that if you take a backup and create a new database from that backup the empty extents are not backed up, so that data could not be restored to the new database. But that still leaves some old data in the extents that are still in use. See:

'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

I suppose that overwriting the data first, then deleting it, might help.  But that may not be practical for many reasons. (Too much data to update, constraints, unique indexes, triggers, etc.)


SQL Server--If logic in stored procedure and the plan cache SQL Server 2012 and 2016 Standard:

If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?

And if on the following execution, the value of the parameter changes, will it re-compile and re-cache the stored procedure, since a different branch of the code must be executed? (This query is quite expensive to compile.)

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?

No, it caches all versions. Or rather, it caches one version with all paths explored, compiled with the first set of passed in variables. Cardinality estimation for all plans will be done using them. This can be extra bad if some passed in values are NULL.
Here's a quick demo, using the Stack Overflow database.
Create an index:
CREATE INDEX ix_yourmom ON dbo.Users (Reputation) INCLUDE (Id, DisplayName);
GO

Create a stored procedure with an index hint that points to an index that doesn't exist, in branched code.
CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS
BEGIN

    IF @Reputation = 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;
    END;

    IF @Reputation > 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = ix_yourdad)
        WHERE u.Reputation = @Reputation;

    END;

END;

If I execute that stored proc looking for Reputation = 1, I get an error.
EXEC dbo.YourMom @Reputation = 1;

Msg 308, Level 16, State 1, Procedure YourMom, Line 14 [Batch Start
Line 32] Index 'ix_yourdad' on table 'dbo.Users' (specified in the
FROM clause) does not exist.

If we fix the index name and re-run the query, the cached plan looks like this:

Inside, the XML will have two references to the @Reputation variable.
<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" />

A slightly simpler test would be to just get an estimated plan for the stored proc. You can see the optimizer exploring both paths:

And if on the following execution, the value of the parameter changes,
will it re-compile and re-cache the stored procedure, because a
different branch of the code must be executed? (This query is quite
expensive to compile.) Thank you.

No, it will retain the runtime value of the first compilation.
If we re-execute with a different @Reputation:
EXEC dbo.YourMom @Reputation = 2;

From the actual plan:
<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(2)" />

We still have a compiled value of 1, but now a runtime value of 2.
In the plan cache, which you can check out with a free tool like the one my company develops, sp_BlitzCache:

The stored procedure has been called twice, and each statement in it has been called once.
So what do we have? One cached plan for both queries in the stored procedure.
If you want this sort of branched logic, you'd have to call sub-stored procedures:
CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS
BEGIN

    IF @Reputation = 1
    BEGIN

        EXEC dbo.Reputation1Query;

    END;

    IF @Reputation > 1
    BEGIN

        EXEC dbo.ReputationGreaterThan1Query;

    END;

END;

Or dynamic SQL:
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql +=
N'
SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u '
IF @Reputation = 1
BEGIN
    SET @sql += N' (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;'
END;

IF @Reputation > 1
BEGIN

SET @sql += ' WITH (INDEX = ix_yourmom)
        WHERE u.Reputation = @Reputation;'

END;

EXEC sys.sp_executesql @sql;

Hope this helps!


Load & traffic balance best practices I am about to design an enterprise solution for a large scale application in which a windows service, MVC, and many other projects will use a database, so I am thinking about load balancing and best practices to organize my database.

I have two options :

use different schemas (I know schema is much like a folder)
use different databases

From the point view of database design which is better, and what's the best practice?

If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague).

I typically advocate for separate databases, though, for a variety of reasons:

You seem to be separating on things that are functionally different - there is no real advantage to containing data for logically separate apps in the same database, whether you use schema separation or not - unless they truly do share the same copy of the data.
Easier to split out a database onto a separate server if it scales faster or outgrows the server it's on. Even with separate schemas it will be tedious to move just one app's data out of that database; if they start in their own database, it's as simple as backup, restore on the other server, and change the connection string for that app. If you have one database and you outgrow the server, you have to move the whole thing. And then again next time.
Easier to manage recovery separately - you may want use full recovery and frequent log backups for your source control, but don't need such rigor for the database that holds employees' favorite Power Ranger characters. You can also avoid having to restore all applications to the same point in time if you have to revert on e of the databases for some reason.
It sounds like more work to manage this, but really, you can make it so that managing 2 databases is not really any different from managing 2,000 databases.

In a previous life I managed a system with many databases; they were one per client with (largely) identical tables/views/procedures. I've answered multiple questions here based on my experience and knowledge through that architecture (including some drawbacks to be aware of in the first answer):

Is that a good idea to use one database for 50.000+ shops?
Handling growing number of Tenants in Multi-tenant Database Architecture
Automation of backups of Large Number of Database
At what point does one database per client become unfeasible?

I have yet to hear anyone argue successfully for storing different data (different apps, different customers, etc.) in the same database. I think anyone who chooses to do this is either confident that their data will never outgrow that server, or is focusing on the near term goal of simplicity & minimal effort (which can be quite short-sighted, and bite you in the long run).


Speedup MYSQL(8.0.13) Restore from .sql file I am receiving .SQL(500mb) backup file and restoring to MYSQL (8.0.13) database.
Currently, restore is taking 5 hrs to complete. I would like to speedup the process and started working on it.
Here are the things I tried

Changed innodb_flush_log_at_trx_commit=2 from 1.

Changed expire_log_days from 0 to 2. My log was filling up and storing file for everyday.

My .sql file already have Unique key,Foreign key checks to zero.AutoCommit=0 like below
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

Here are my my.ini file settings
 max_connections=151
 table_open_cache=2000
tmp_table_size=172M
 thread_cache_size=10
myisam_max_sort_file_size=100G
 myisam_sort_buffer_size=334M
key_buffer_size=8M
 read_buffer_size=64K
 read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=2
 innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
 innodb_log_file_size=48M
Innodb_thread_concurrency=17
 innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
 innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
 innodb_open_files=300 i
nnodb_stats_on_
metadata=0
 innodb_file_per_table=1
flush_time=0
join_buffer_size=256K
 max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
 sort_buffer_size=256K
 table_definition_cache=1400

frustrating part is sometimes restore is stuck for hours and I have to kill the connection.
Please let me know what I am doing wrong here.
Appreciate your help.

innodb_buffer_pool_size should be closer to 1G rather than 8M
also increase innodb_log_file_size to 256M as the default 48M is a bit slow.
A innodb_log_buffer_size of 48M would help rather than a very small 1M.


SQL Server 2008 error when changing the number of cores I have a virtual machine (virtualbox) with Windows Server 2003 and SQL Server 2008, running with 4 cores. I wanted to use 6 cores, but when I changed the machine configuration, for some reason SQL Server stopped working. I couldn't start the service - it was giving me an unhandled exception without any extra data.

Does anyone know a fix for that? I can live with 4 cores, but I really want to upgrade to 6.

(I know is this setting, because as soon i went back to 4 cores SQL Server started to work again)

I think there has to be a way to upgrade the machine without reinstalling.

Check the version of SQL Server. Enterprise version may be required.


Get rows above and below (neighbouring rows) a certain row, based on two criteria SQL Say I have a table like so:
+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 3 |   1   |  90  | 2021-01-12 15:38:0  |
| 1 |   1   |  20  | 2021-01-14 13:10:12 |
| 5 |   1   |  50  | 2021-01-13 12:32:11 |
| 7 |   1   |  50  | 2021-01-14 17:15:20 |
| 8 |   1   |  55  | 2021-01-14 09:20:00 |
| 10|   2   |  99  | 2021-01-15 10:50:38 |
| 2 |   1   |  45  | 2021-01-15 10:50:38 |
+---+-------+------+---------------------+

What I want to do is show 5 of these rows in a table (in html), with a certain row (e.g. where id=5) in the middle and have the two rows above and below it (in the correct order). Also where level=1. This will be like a score board but only showing the user's score with the two above and two below.
So because scores can be the same, the timestamp column will also need to be used - so if two scores are equal, then the first person to get the score is shown above the other person.
E.g. say the user is id=5, I want to show
+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 8 |   1   |  55  | 2021-01-14 09:20:00 |
| 5 |   1   |  50  | 2021-01-13 12:32:11 |
| 7 |   1   |  50  | 2021-01-14 17:15:20 |
| 2 |   1   |  45  | 2021-01-15 10:50:38 |
| 1 |   1   |  20  | 2021-01-14 13:10:12 |
+---+-------+------+---------------------+

Note that id=7 is below id=5
I am wondering does anyone know a way of doing this?
I have tried this below but it is not outputting what I need (it is outputting where level_id=2 and id=5, and the other rows are not in order)
((SELECT b.* FROM table a JOIN table b ON b.score > a.score OR (b.score = a.score AND b.timestamp < a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score ASC, timestamp DESC LIMIT 3)
 UNION ALL
 (SELECT b.* FROM table a JOIN table b ON b.score < a.score OR (b.score = a.score AND b.timestamp > a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score DESC, timestamp ASC LIMIT 2))
order by score

If it is easier to output all rows in the table, say where level = 1, so it is a full score board.. and then do the getting a certain row and two above and below it using PHP I'd also like to know please :) ! (possibly thinking this may keep the SQL simpler)?

The values for id 5 and LIMIT must be calculate beforehand and passed on to the query, because LIMIT must have fixed values or you have to use a prepared statement

CREATE TABLE table1
    (`id` int, `level` int, `score` int, `timestamp` varchar(19))
;

INSERT INTO table1
    (`id`, `level`, `score`, `timestamp`)
VALUES
    (4, 1, 70, '2021-01-14 21:50:38'),
    (3, 1, 90, '2021-01-12 15:38:0'),
    (1, 1, 20, '2021-01-14 13:10:12'),
    (5, 1, 50, '2021-01-13 12:32:11'),
    (7, 1, 50, '2021-01-14 17:15:20'),
    (8, 1, 55, '2021-01-14 09:20:00'),
    (10, 2, 99, '2021-01-15 10:50:38'),
    (2, 1, 45, '2021-01-15 10:50:38')
;

SELECT
    `id`, `level`, `score`
FROM
    ((SELECT
        `id`, `level`, `score`, 1 orderby
    FROM
        table1 a
    WHERE
        level = 1
            AND `score` > (SELECT
                `score`
            FROM
                table1
            WHERE
                id = 5)
    ORDER BY `score` ASC
    LIMIT 2) UNION ALL (SELECT
        `id`, `level`, `score`, 2 orderby
    FROM
        table1 a
    WHERE
        id = 5) UNION ALL (SELECT
        `id`, `level`, `score`, 3 orderby
    FROM
        table1 a
    WHERE
        level = 1
            AND `score` <= (SELECT
                `score`
            FROM
                table1
            WHERE
                id = 5)
            AND id <> 5
    ORDER BY `score` DESC
    LIMIT 3)) t1
ORDER BY orderby ASC , score DESC

id | level | score
-: | ----: | ----:
 4 |     1 |    70
 8 |     1 |    55
 5 |     1 |    50
 7 |     1 |    50
 2 |     1 |    45
 1 |     1 |    20

db<>fiddle here


how to update SSRS execution account automatically through power-shell? I need to update the password for the SSRS Execution account for every 15 days. How can I automate this process using power shell or some other scripting?

Changing the passwords:

If you are using a Active Directory account you can use the ActiveDirectory PowerShell module.

Set-ADAccountPassword -Identity elisada -OldPassword (ConvertTo-SecureString -AsPlainText "p@ssw0rd" -Force) -NewPassword (ConvertTo-SecureString -AsPlainText "qwert@12345" -Force)

If you are using a Sql Server user than it would be using T-SQL

ALTER LOGIN UserName WITH PASSWORD = 'P@ssw0rd';
    GO

Updating the Service:

$service = gwmi win32_service -computer [computername] -filter "name='ReportServer'"
$service.change($null,$null,$null,$null,$null,$null,$null,"P@ssw0rd")


SQL Server 2014 "syspolicy_purge_history" job keeps failing I am using SQL Server 2014 and the job "syspolicy_purge_history" keeps failing at step 3 "Erase Phantom System Health Records" and its driving me crazy as I don't know what is causing the failure.

here is the command:

if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()

The error message I got was:

  Executed as user: jonbrown. A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\xyz\T$a).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Failed to connect to server .  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)  '.  Process Exit Code -1.  The step failed.

Can you try this in the Windows Server PowerShell path?

PS C:\Windows\system32> Get-ExecutionPolicy -list

     Scope            ExecutionPolicy
     --------         ------------------
     MachinePolicy    Undefined
     UserPolicy       Undefined
     Process          Undefined
     CurrentUser      Undefined
     LocalMachine     Restricted

..and then the same in the SQL Server path:

PS SQLSERVER:\> Get-ExecutionPolicy -list

     Scope            ExecutionPolicy
     --------         ------------------
     MachinePolicy    Undefined
     UserPolicy       Undefined
     Process          RemoteSigned
     CurrentUser      Undefined
     LocalMachine     Restricted

If the execution policy is set to “Restricted”, try to set it to “RemoteSigned” or “Unrestricted”.

Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy Unrestricted

or

Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy RemoteSigned

Please note that you need to set this execution policy in both the Windows PowerShell and the SQL PowerShell consoles.


ConnectException: 'Connection refused' My node worked normally, and I could use nodetool status normally, but when I changed endpoint_snitch to Ec2Snitch I started getting the error:

  nodetool: Failed to connect to '127.0.0.1:7199' - ConnectException: 'Connection refused'.

Although it worked fine when endpoint_snitch was SimpleSnitch

The node private address is 10.0.5.247 and attached to security group that has 7199 open for inbound and outbound for the range that covers 10.0.5.247

So, any idea?

Ec2Snitch is specifically designed for Cassandra being installed in AWS EC2 instances. Please confirm if your installation is in AWS? In case you are, check whether listen address in cassandra.yaml is set to the private ip of the node (instead of localhost)

SimpleSnitch should work fine for Single datacenter. Its recommended to use GossipingPropertyFileSnitch for production.


how to Protect certificate in sql server I am using symmetric key encryption in SQL server but the problem is that if anyone has permissions to explore the database they can easily view certificates and symmetric keys and can therefore access encrypted data using these keys. How can this be prevented?

While it's true that anyone with read permissions can browse the database using SQL Server Management Studio, additional permissions need to be granted to use the keys for decrypting the data. The first point to keep in mind is that a certificate is a public object, and it's the related private key that needs protection to ensure that it can't be used by unauthorized users to decrypt  the symmetric key and subsequently the data. The private key is encrypted by the Database Master Key and is not visible to any user, even to those in the sysadmin role. Just because a user can see the objects doesn't mean they can use them. The permissions needed to decyrypt data using the encryption hierarchy you described, which is a symmetric key protected by certificate protected by the database master key, are CONTROL on the certificate and REFERENCES on the symmetric key. Without both of those permissions, decryption will not be possible. However, if you grant the CONTROL permission on the certificate directly to the user or the user is in the db_owner, db_ddladmin or db_securityadmin role, then the user can back up the certificate and private key, which is a threat to the security of your encryption. In addition to removing the users from the aforementioned roles, there are several ways to address this issue. One is to use an asymmetric key in your hierarchy instead of a certificate, since there is no backup command for asymmetric and symmetric keys. The other is to use code signing and only grant the permissions to a certificate user. Someone also mentioned that it may be possible to use impersonation , or "execute as" in a procedure, but this may not be the safest choice. If you want to replace the certificate with an asymmetric key in your hierarchy, then you should back up the Database Master Key for the database and backup the database, then restore both in a non production environment so you can create the script and test it completely before attempting this in a production environment.

If you really want to hide the names of all of the objects, then you can deny view definition to the users in the database. Again, test in non-production first.


Recovering from Oracle host OS disk failure (options)? If an Oracle host OS has a complete disk failure and the host can be restored (assuming from a recent clone of the host OS disks) from backup, is it possible to then point the host to the existing Oracle datafiles on a SAN, as they were before the host disk was lost, and bring the database back up?  This is assuming we are using Oracle ASM and that there were no OS or Oracle configuration changes made on the OS between the imaging of the host drive(s) and the drive failure.

There is nothing 'magical' about Oracle and its recover ability. As long as you have your ASM metadata somewhere available, you can import it on a spare host and use that in a fresh installation to attach your SAN hosted files/LUNs and use that to continue your run on. Important is to know that all your data is in ASM and all your ASM disks are on the SAN. If you do happen to have some files containing application data are left on the Server that ran your databases before it crashed, it might cause an extra challenge.

Knowing that it is technically possible is one thing, the ability to perform such a recovery is an other one. The only way to gain that ability is to train for it. Each OS has some things that make it more or less easy to do. This is for any failure scenario.


How can one fetch subtotal in relation to a group by from an outer query? I have a simple query,  where I would like to display for past 7 days, grouped by hour from a table apiRequest: The count of request having a transaction vs the count of those having no transaction.

I tried two different approaches but both failed. The first column displays fine, it is the second one that is problematic:

+--------------+-------------+--------------------+-----------------+
| Date         | Time        | Total Requests     | no trans.       |
+--------------+-------------+--------------------+-----------------+
| January 20th | 2:00-3:00   |                 44 |               0 |
| January 20th | 3:00-4:00   |                 32 |               0 |
| January 20th | 5:00-6:00   |                 51 |               0 |
| January 20th | 6:00-7:00   |                 18 |               0 |

First approach was with a subquery, that nearly worked but I could not get the WHERE accountDetails NOT LIKE "%Transactions\":[{%" to work.

Second query was with an inner join, but could not manage to get the group by to work as expected for second column.

--  Two column table that displays number of requests for past seven days, grouped by hour of the day
-- First try, with subquery - the second column does not show result

--  Col 1 - Shows count of requests with transactions,
--  Col 2 - count of requests without transactions
--  for every hour of the day in past seven days
SELECT DATE_FORMAT(a.created, "%M %D") Date,
       CONCAT(HOUR(a.created), ':00-', HOUR(a.created)+1, ':00') Time,
       count(a.`RequestId`) as "Total Requests",
         (select count(a2.`RequestId`) FROM apiRequest a2 WHERE DATE_FORMAT(f.created,"%Y %M %D %H") = DATE_FORMAT(a2.created, "%M %D %H")) "IBV's no trans."
        FROM apiRequest a
   WHERE a.created >= CURDATE() - INTERVAL 7 day
     AND a.RequestId IS NOT NULL
GROUP BY DATE_FORMAT(a.created, "%M %D %H");

-- Second try, with inner join - group by does not work
SELECT DATE_FORMAT(a.created, "%M %D"),
       CONCAT(HOUR(a.created), ':00-', HOUR(f.created)+1, ':00') AS Hours,
       count(a.`RequestId`) as "Total requests",
       f2.ibvsNoTrans
        FROM apiRequest a
        INNER JOIN (SELECT COUNT(RequestId) ApiNoTrans FROM ApiRequest WHERE accountDetails NOT LIKE "%Transactions\":[{%" GROUP BY date_format(apiRequest.created, "%Y %M")) f2
           WHERE a.created >= CURDATE() - INTERVAL 7 day
     AND a.RequestId IS NOT NULL
GROUP BY YEAR(a.created), MONTH(a.created), DAY(a.created), HOUR(a.created), Hours;

I am quite certain I am close to the goal but missing a detail.
I feel this should be simpler, what is the proper approach, and is there a more optimal manner to match on dates / hours than what I have?

In order to get the missing data, first you need to create a table with all the hours.  Then LEFT JOIN from that table to your table.

You may also need a COALESCE(..., 0) to turn NULL into 0.


Find IDs in table where at least two entries exist with that ID and an additional field is A for one and B for the other entry I have a game where we store user results in a table, similar to this structure:

game_id, user_id, score, ....

Now our service team would like to find all game_ids where user A and user B played together. Meaning I want to SELECT and GROUP BY game_ids WHERE one entry has user_id = A and the other entry has user_id = B.

What would be a performant way to query this in MySQL?

find all game_ids where user A and user B played together

SELECT t1.game_id
FROM table t1, table t2
WHERE t1.game_id = t2.game_id
  AND t1.user_id = 'A'
  AND t2.user_id = 'B'

If you need the same for 3, 4 or more users, you can add more table copies, or use

SELECT game_id
FROM table
WHERE user_id IN ('A', 'B', 'more users')
GROUP BY game_id
HAVING COUNT(user_id) = :users_count:

If you want to use users list as a parameter, you can use

SELECT game_id
FROM table
WHERE FIND_IN_SET(user_id, :comma_separated_users_list:)
GROUP BY game_id
HAVING COUNT(user_id) = :users_count:


Which SQL Server Express edition is right for me? I have developed a Java program which works with SQL Server as it's database. I have installed SQL Server 2012 Enterprise edition on my Windows and designed the database via SQL Server Management Studio; for example: I have designed its tables, have set a backup schedule and it takes a backup from itself every 24hs once, and other features.

Which version of SQL Server Express should be installed on a user's Windows computer so that not only can my program connect to a local db and work with that, but also the other proscribed database features (like backup schedule) would work?

The database and Java program are going to be on the same computer.

The criteria I have for chosing a database edition are

My database has some tables
The Java program is going to insert, update, delete, select, and add columns to tables
iReport will be used to report on the data, in addition to the Java application
The database file is set so that it can not be deleted
It has a scheduled backup and nothing else

Backup are automated via SQL Agent jobs in SQL Server.  Since no express edition includes the SQL Server Agent, then you would have to find a way to have your application initiate the backups. Otherwise, you need standard edition.

What other features are you using?  Some of them may require enterprise or standard.  Personally when I was doing a lot of development, I would try to use an edition that matched what an average target user would have.  So If I want them to have SQL Express, then that is what I used for my development.


Postgres 10.3 : table grows in size after delete-insert I'm a rather new to Postgres and the issue I see seems a bit strange. Maybe you as experts know something about it.

I have a table (around 3 million rows and 200 columns). In order to populate fresh data this table gets dropped and fully reloaded on a regular basis. I didn't like this approach with full reload so I changed the process a little bit: now the records that require recalculation are deleted and loaded from scratch - other data stays intact. This approach is significantly faster than the full reload, however after deletion and insertion the table bloats horribly (3-5 times depending on the number of records deleted-inserted).

I do understand that the data is not physically removed when DELETE statement is executed. I know that dead tupples remain. My expectation was that if I dropped all the indexes before deleting data, partially deleted data, did VACUUM ANALYZE, reloaded the data and built indexes once again (exactly in this sequence) neither the table itself nor indexes would bloat. But apparently, I was wrong.

Is there anything substantial that I am missing here?

UPDATE: Funny thing is that "SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'table_name' " shows that there are no dead tuples. Now I'm puzzled even more.

UPDATE 2: I figured why the reason behind the bloating. There's another step after the "delta" reload that updates a couple of columns for the whole table (because they can't be loaded in delta mode). This update makes the table grow in size.

VACUUM will only shrink the data file if the free space happens to be at the end. What you probably want is a VACUUM FULL. However be aware of a couple of caveats:

It will lock the table while it runs (which may be a while)
It will require equivalent temporary space to the table

But when it is finished, the final space used will be just enough for the actual data you have.


Mutating Error , How to solve it CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_FA          FUND_DISBURSE.FUND_AMT%TYPE;
V_RC          NUMBER (10);
V_AC          FUND_DISBURSE.AREA_CODE%TYPE;
V_BC          FUND_DISBURSE.BRANCH_CODE%TYPE;
V_CC          FUND_DISBURSE.COMP_CODE%TYPE;
V_DN          FUND_DISBURSE.DAFA_NO%TYPE;
V_PRN         NUMBER (15,2);
V_SC          NUMBER (15,2);
V_INSN        NUMBER (2) :=0;
V_INSD        FUND_DISBURSE.DISBURSE_DATE%TYPE;
V_RAMT        NUMBER (15,2);
BEGIN
select AREA_CODE, BRANCH_CODE, COMP_CODE,DAFA_NO,DISBURSE_DATE,FUND_AMT
into
V_AC,V_BC,V_CC,V_DN,V_INSD,V_FA
from FUND_DISBURSE
WHERE AREA_CODE = :NEW.AREA_CODE
and BRANCH_CODE = :NEW.BRANCH_CODE
and COMP_CODE = :NEW.COMP_CODE
and DAFA_NO = :NEW.DAFA_NO
AND DISBURSE_DATE =:NEW.DISBURSE_DATE
AND FUND_AMT = :NEW.FUND_AMT;
V_RAMT := V_FA*1.13;
V_INSD := (V_INSD-7) + 14;
for i in 1..50 loop
V_INSD := V_INSD+7;
V_RC := V_FA/1000*25;
V_PRN := V_RC/1.13;
V_SC := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC,
V_PRN,V_SC );
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/

Trigger complied, but can not insert any record.
 table is mutating, trigger/function may not see it

How can I solve it. Please suggest.

Sorry again. Thanks to ***mat and all* for helping me**.
I have understood.
Here my new code after correction.

CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RC          NUMBER (10);
V_PRN         NUMBER (15,2);
V_SC          NUMBER (15,2);
V_INSN        NUMBER (2) :=0;
V_RAMT        NUMBER (15,2);
v_insd        date;
BEGIN
V_RAMT := :NEW.FUND_AMT*1.13;
V_INSD := (:NEW.DISBURSE_DATE -7 ) + 14;
LOOP
V_INSD := V_INSD+7;
V_RC   := :NEW.FUND_AMT/1000*25;
V_PRN  := V_RC/1.13;
V_SC   := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC, V_PRN,V_SC);
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/

the code erro ORA-04091 is relative the transaction. In a trigger you dont can execute select, unless you declare a new transacation with clause PRAGMA AUTONOMOUS_TRANSACTION.

remember to run a commit or rollback to finalize transaction on the trigger.


Store extra, optional information about any entity I've done a bit of reading but haven't found anything related. This must be widely discussed, so it's most likely my fault.

I've got a relational database with a number of different tables, each storing their unique "thing". Now I'd like to be able to attach extra information (0 or more) to any entity. A "custom field" if you want.

My first instinct was to have GUID PKs in every table, so that I could reference anything in the database by that GUID. I'd have another table for those extra attachments, where I could attach stuff to those GUIDs, no matter which table they stem from.

I don't want to introduce GUIDs for no good reason. And I fear this isn't one.

An alternative would be to reference the items by "table name" and "PK". This is easy as long as the PK is an IDENTITY INT, but AFAIK forcing a surrogate key is bad practice. Also referencing by "table name" feels wrong.

Am I missing something obvious? Maybe the whole approach of attach stuff to any item in this db is dubious.

EDIT:

I'm talking about a single table that stores the extra information belonging to the "main data" tables.
Let's think of the extra information as a field "comments".
I would utilize an extra table in order to accomplish a one-to-many relationship. One row main data may have N 'comment' entries.
The extra information may be attached to any of the "main" entities.
'use how often': it's non-essential information. The business data should not rely on it. For me, it's mostly an academic question, but if it turns out to be good design, I could imagine putting it to use.
'accuracy': I don't understand - the data should be as safe as the main data.

EDIT2:

Here's a different use-case involving a similar principle:

Users should be able to suggest typo corrections to data fields they encounter. I.e. user Sandy thinks a product name is misspelled, so I want her to add a row to a table corrections. This entry has to reference the original product row as well as the field name. A correction would reference the table, the exact row by PK and the field she wants changed (and -obviously- a new value for it).

Question still remains: how to reference any field/row for meta-information?

Suggestions

My first suggestion is that the business database is not the right place to store comments on what it contains.

Keep the comments in your issue tracker instead. A good issue tracker provides all the features to meet your requirements.

If you still want to do this in the database, research the history table pattern. See this Stack Overflow question for design ideas.

Essentially, you'd have an extra table for every table that you want to store comments about. Each table would refer to the business data by primary key, and would store the name, date, revision of the comment as well.

I've stated my assumptions below. Please correct me so we can help you better.

Assumptions

You have an issue tracker. It's regularly backed up and you can restore from backup.

You want to store instructive or descriptive text for some of the business data.

You would distinguish multiple comments by date of entry and by order of entry.

Most of the data would have no comments.

You want to be able to search the comments. Comment search queries run seldom compared to business queries.

You want to keep comments on deleted data.

You don't need to programatically cross-reference the business data and the comments or analyze them together. You just need to view and edit complete comments.

The comments do actually have value to the business (You are part of the business, right?).

Greater comment visibility would benefit the business.

Your business would react badly if the comments were lost.


When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF SO i have a rowset of 10 UIDs. They are all dupes, lets say DUPEUID. I have a UDF that will take the UID, check it against the table to see if there is a dupe, and update the UID to DUPEUID01.

The problem i'm having is that my update statement updates a rowset. The first row gets updated, but when the 2nd row is evaluated  the previous updated row is not found during the SELECT. I suspect i am running into an isolation issue.

I have tried REPEATABE, SERIALIZABLE, to no avail.

is there no way to do this in a rowset operation? DO i have to create a cursor and loop through all the dupes?

Here's a rough example:

there are 3 rows

UID
-------
UIDDUPE
UIDDUPE
UIDDUPE

all 3 rows same value.

UDF that goes and does this:
select * from uid table looking for dupes.

and an update that calls the UDF

update table
set UID = dbo.UIDFIX(UID)

when the update runs , it updates the first row to UIDDUPE01, when it gets to the 2nd row, it does a select,and does NOT find the previously updated row. so it creates ANOTHER UIDDUPE01.

The logic for updating these UIDS are much MORE complicated than the simple way i've shown it here. The accepted answer will stick to my question, and not say well you shouldnt' use..... This is the real world. sometimes we have horrible situations.

The question has to do with UDF's and set based consistency?

(You edited your question, so now I'm editing my answer)

Stop using a UDF for it. Do it all in one hit like this:

WITH Numbered AS
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
UPDATE Numbered SET UID = UID + '_' + CAST(DupeNum AS VARCHAR(10))
WHERE DupeNum > 1;

By doing it all at once, you will avoid the issues you have, because it works out what they should all become and applies them in a single transaction.

Also - put an underscore or something in. If you have UIDs like D, D, D2, then it's better to end up with D, D_2, D1 than D, D2, D2. Use a character that isn't used in regular UIDs. And ideally, store what it was originally somewhere, because if you get more Ds coming through, you want to be able to tell that you really have a third duplicate, not just a second one.

A further edit to clarify:

A scalar UDF is a procedure. It has BEGIN and END, and runs row by row. When this runs repeatedly in a larger query, it runs within the context of the transaction, and therefore sees the impact of earlier work within the transaction.

If you MUST use the UDF, create a temporary table containing the rows that you want to update to, and then perform an update by joining this temporary table. While you populate the temporary table you're not making any changes to the table, so every UDF call will be unaffected by those changes. Then you can do it without having a UDF in your actual update statement.

Yet another edit;

What does this do?

WITH Numbered AS
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
UPDATE Numbered SET UID = dbo.UIDFIX(UID)
WHERE DupeNum > 1;

And more...

How about:

--First get the new values
WITH Numbered AS
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
SELECT *, dbo.UIDFIX(UID) AS NEWUID
INTO #NewVersion
FROM Numbered
WHERE DupeNum > 1;

--Now update them all
UPDATE theTable
SET UID = n.NEWUID
FROM #NewVersion AS n
WHERE n.PK = theTable.PK;


MySQL on Windows or GNU/Linux? I've just read this article:
http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html

There it says that MySQL on Windows is a good choice. (or is what I've understood)

In the article I don't understand if he says that MySQL on Windows is better than on Linux, or just that MySQL on Windows is good enough as Linux.

Now my doubt is:
if I can choose a server dedicated to MySQL and I have to choose between Windows Server 2012 and a free GNU/Linux server distro, which one would have better performances?

May you guys help me understand this?

There are advantages of using Linux as you base for mysql, as others have already covered, but from a performance point of view for many applications you will not see the difference particularly.

While Linux would be my default recommendation too make sure you consider your experience (and that of the people you can immediately call upon if something goes wrong). If you are familiar with Windows but have little sysadmin experience with Linux, go with Windows. Having the knowledge to fix certain things when they go wrong may prove much more important than a performance gain while things are going right, and if got know Windows like the back of your hand you may be able to tune it to the point where it performs better than a similar Linux install that you might produce. Likewise if you have a preference for or more experience with Linux go with that for the same reasons.


SQL Server 2008 Setup Error 0x80070490 I am trying to install SQL Server 2008 x64 on Windows 2008 R2 and keep getting the following error:

  SQL Server Setup has encountered the following error: Element not
  found. (Exception from HRESULT: 0x80070490)

I have applied all required patches and there are no other instances of SQL Server on the machine.

Any clues as to what the cause might be?

Thanks.

The error is caused if there is offline and removed disks inside the cluster (that are no longer presented to the server nodes). Removing the disks and re-running the installer fixed the problem.