I have this existing table for IP storage:
CREATE TABLE [dbo].[IPAddresses]( [ID] [int] IDENTITY(1,1) NOT NULL, [IPv4Address] [varchar](15) NULL, [IPv6Address] [varchar](45) NULL, CONSTRAINT [PK_IPAddresses] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UniqueIPv4Address] UNIQUE NONCLUSTERED ( [IPv4Address] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
I want to convert this table to use an integer (or several integers) to store this IP information. I would like to make
IPv6Address computed columns based on my new columns for storage.
The problem I'm facing is that I have some legacy clients hitting this database, who need to write directly '192.168.1.54' into my
IPv4Address column. I would like the database to intecept this, do a conversion to integer, and store it in a new column on the table defined as
Is it possible to
- Script a conversion of my existing table, converting all of my "string" IPs to their integer values and storing those in an int column, and making these "string" columns computed
- Allow legacy clients to "write" to these computed columns and intercept that data, convert it to an int, and store it transparently to legacy clients?
I am fairly sure I can write a script to conver the data, but I don't want to waste my time if I cannot make this transparent to legacy clients of my database. So any guidance will go a long way.