Create Index equivalent from Oracle to SQL Server


Create Index equivalent from Oracle to SQL Server



I have a current index that I have pulled from Oracle and I am trying to add to SQL Server. As far as I can tell, I will have to remove the double quotes and use WITH(... However, I cannot find the Maxtrans and other keywords equivalent in SQL Server, especially the M_ROW$$, which I was thinking it would be the ROW_NUMBER.


WITH(...


M_ROW$$


ROW_NUMBER


CREATE UNIQUE INDEX ""MYDB"".""I_SNAP$MYTABLE"" ON
""MYDB"".""MYTABLE"" (""M_ROW$$"")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE ""IL_MIS_INDX""



Any help will be greatly appreciated.



Thank you!





M_ROW$$ is the name of a column on the table I_SNAP$MYTABLE. As far as I can see it has no relation to ROW_NUMBER.
– Bob Jarvis
Jun 29 at 19:47


M_ROW$$


I_SNAP$MYTABLE


ROW_NUMBER





Those double-doublequotes aren't an Oracle thing. If you create a table named XYZ then tools tend to generate DDL as CREATE TABLE "SOMEUSER"."XYZ" to be on the safe side, with all the storage defaults like PCTFREE 10 INITRANS 2 MAXTRANS 255. Yes you can remove all double quotes unless they are explicitly preserving some non-standard name. MAXTRANS is deprecated, and the other storage parameters are ignored anyway for automated segment space management, which has been the default for a long time.
– William Robertson
Jun 29 at 23:12


XYZ


CREATE TABLE "SOMEUSER"."XYZ"


PCTFREE 10 INITRANS 2 MAXTRANS 255


MAXTRANS





By the way, it looks like I_SNAP$MYTABLE is a materialized view log, which appears not to have any equivalent in SQL Server. It's possible that it's not needed.
– William Robertson
Jun 30 at 7:03



I_SNAP$MYTABLE




1 Answer
1



I'm guessing you don't need all the options. So, just use the default syntax:


CREATE UNIQUE INDEX <MYDB.I_SNAP$MYTABLE> ON MYDB.MYTABLE (<M_ROW$$>)



Of course, the table name and column name needs to be the actual names in SQL Server.



I am speculating that you don't need the index options in SQL Server. If you do, you will need to optimize the index for that database, rather than assuming that what works in Oracle will automatically work in SQL Server.





Gordon, Thank you for your answer. I was trying to keep the same structure in both environments. Of course, if that is not a possibility, I can definitely scale it down. However, if I can keep as close as possible, the better.
– 1moreLearner
Jun 29 at 19:03





What Gordon wrote preserves all logical structure, all other stuff is just options which are database engine dependant, may affect performace only and are not 1:1 portable
– avb
Jun 29 at 19:11






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Comments

Post a Comment

Popular posts from this blog

paramiko-expect timeout is happening after executing the command

paramiko-expect timeout is happening after executing the command Can someone help me why timeout is happening after executing the command .I am trying to SSH to a machine and execute a command.I want to store the result of the executed command . I have referred paramiko-timeout but i didn't get my expected result. import traceback try: import paramiko except ImportError: raise Exception("Please install paramiko , pip install paramiko") import pexpect from paramiko_expect import SSHClientInteraction def main(): HOSTNAME = "minion5.net" USERNAME = "root" PASSWORD = "help@432" PROMPT = "root@*:~$s+" try: client = paramiko.SSHClient() client.load_system_host_keys() client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) client.connect(hostname=HOSTNAME, username=USERNAME, password=PASSWORD) with SSHClientInteraction(client, timeout=20, display=True) as interact: ...
Read more

Export result set on Dbeaver to CSV

Export result set on Dbeaver to CSV Normally I use Dbeaver for windows and always export my result set like this: run my query --> select the result --> export the result set --> select export to clipboard --> done This step by step puts my result set in my clipboard and I can paste it wherever I want to work with it. The problem is that now I am using dbeaver for mac and this guide is not working. I can go on until the moment that I select my result set like in the image below: exporting data set But once I go further in the process, in the last step I get: no query Note that in "source" it was suppose to show the query that originated the result set, but instead it says just "select. As a result it does't select my result or anything (besides being "successful"). Normally my query would show up there automatically and I couldn't find any option that corrects this problem in the menus. Please help me! Thanks. Aft...
Read more

Opening a url is failing in Swift

Opening a url is failing in Swift breakpointing inside the callback and logging success shows false. I added itms-apps to my plist: LSApplicationQueriesSchemes and put this in my app delegate: func application(_ app: UIApplication, open url: URL, options: [UIApplicationOpenURLOptionsKey : Any] = [:]) -> Bool { return true } func moreTapped() { let url = NSURL(string: "itms-apps://itunes.com/developer/quantum-productions/id979315877") if #available(iOS 10.0, *) { UIApplication.shared.open(url! as URL, options: [:], completionHandler: {(success: Bool) in }) } else { UIApplication.shared.openURL(url! as URL) } } Are you testing on a real device? – rmaddy Jun 21 at 23:57 I try on a device, get "Cannot Connect to App Store". Device is online ...
Read more